CS 1555 / 2055 – Database Management Systems (Fall 2020) Dept. of Computer Science, University of Pittsburgh Assignment #7: Conceptual Database Design – E-R Model Release: Nov. 13, 2020 Due: 8:00 PM, Nov. 20, 2020. Goal: Gain familiarity with conceptual database design and refinement/tuning of the relational database schema. You will design a database using the E-R Model and convert it to a relational schema. Natural Language Description Design a database system MyAuction that facilitates the operations for an electronic auc- tioning system, similar to “ebay.com”. The system needs to keep track of information of registered users, products that have been put for auction by registered users, bidding history and selling history of each product on auction. Some of the intended uses of the database system include: • For each registered user, record his/her name, address, email, a unique login-name and password. • For each product put for auction, record its name, an (optional) description, one or several categories that it belongs to (e.g., ‘books-and-records’, ‘software’, ‘automobiles’, ‘appliances’, etc). Each product should have a unique auction-id. • Keep track of information about a product for auction such as who is selling it, the min- imum acceptable valid price, auction starting date and its status (i.e., ‘under auction’, ‘sold’, ‘withdrawn’). • Keep track of every bid made by registered users, such as the bidder’s name, the date when the bid was made, and the valid amount of the bid, etc. • If a product was sold successfully, we want to know who bought the product with what bidding price, and when it was sold. • For each product category, record its (unique) name. We want to organize the categories into a hierarchical structure such that one category can contain 0 to 10 subcategories. Please state all assumptions explicitly, as well as any constraints that need to be specified in the database schema. Tasks [Total: 100 points] 1. [70 points] Produce a conceptual schema using the E-R model for MyAuction. You are expected to express the schema in both textual and diagrammatic forms. In order to enhance the readability of your E-R diagrams, you may specify only the key attributes for each entity/relationship type. Key and other attributes should be shown in the textual form. For a single entity type with complex attributes, you are also expected to specify all its attributes in an E-R diagram showing only this entity type. Note that you are free to make reasonable assumptions about the application require- ments as long as you state them explicitly. 2. [30 points] Translate it into a relational schema, specifying primary key PK and foreign key FK and any other stated constraint in the assumptions. For example: STUDENT (StudentID, Major) PK (StudentID) FK (Major) → DEPARTMENT (DeptCode) CHECK (Class IS NOT NULL) What to submit 1. You are required to electronically submit exactly one PDF (.pdf) or Microsoft Word (.docx) file named hw7-
(e.g., hw7-pitt01.pdf or hw7-pitt01.docx). Do not forget to include your name and username (account name) in the beginning of the file. No hand-written solutions are accepted. How to submit your assignment 1. Submit your file (i.e., the PDF or DOCX file) that contains your solution through the class Web-based submission interface you have used for previous Assignments. 2. Submit your files by the due date (8:00pm Nov. 20, 2020). There is no late submission. 3. It is your responsibility to make sure the assignment was properly submitted and on-time. Academic Honesty The work in this assignment is to be done independently. Discussions with other students on the assignment should be limited to understanding the statement of the problem. Cheating in any way, including giving your work to someone else will result in an F for the course and a report to the appropriate University authority. 欢迎咨询51作业君