数据库代写: CSC3170 Database Systems

数据库代写代考 DB Database SQL

CSC3170 Database Systems

The Chinese University of Hong Kong, Shenzhen 2nd Term, 2017-18

Solutions to Term Project
Total Point Possible: 40 points (25% of total marks)

Instruction

  1. Your name (in Chinese and Pingyin) and your student ID must both appear in the first page of

    the report. A report without student name and ID will not be considered a submission.

  2. This is an individual work. You should not share and show your work to your fellow classmates.
  3. The university is very serious about academic honesty. If you adopt someone’s ideas or paraphrase

    someone’s writings (including class notes), you must provide citations in the text and references at

    the end of the report. A reference at the end without a citation in the text is not acceptable.

  4. Your report must be typed and your diagrams must be drawn using a computerized graphical tool.
  5. Your report must be written in English.
  6. Your report is due on May 12 at 9:30 a.m. I will collect them in class.

    Judy and Martin Yan run Dayan Antiques & Home Furnishings Trading Company in

Macau. Their company was a family business inherited from Mr. Yan’s grandfather. For over 50 years, the company has been purchasing antiques and home furnishings and selling them to individual customers and industrial users such as companies, offices, restaurants, etc. Judy and Martin wanted to automate some of their business and hired a software integrator (SI) to conduct the automation project. However, the relationship turned sour due to the slow progress in the project and July and Martin fired the SI. You are now hired to take it. After examining fragmented documents, you find some information done by the previous SI. It is your job to determine whether this information is useful or not for your design. State your assumptions in your report for using each piece of data. Also, you have to watch for missing data and relations.

So far, you find the database schema:

CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, Zip, Phone, Email) ITEM (ItemID, ItemDescription, CompanyName, PurchaseDate, ItemCost, ItemPrice) SALE (SaleID, CustomerID, SaleDate SubTotal, Tax, Total)
SALE_ITEM (SaleID, SaleItemID, ItemID, ItemPrice)

EMPLOYEE (EmployeeID, LastName, FirstName, Phone, Email)

VENDOR (VendorID, CompanyName, ContactLName, ContactFName, Address, City, State, Zip, Phone, Fax, Email)

Sample sales data and sample purchase data are given in Figures 1 and 2, respectively.

1

Figure 1 Sample Sales Data

Figure 2 Sample Purchase Data

July and Martin want the database application to go beyond the only recording of sales. They would like to maintain data on customers, employees, vendors, sales, and items, but they hope to (a) modify the way it handles inventory, and (b) simplify the storage of customer and employee data.

2

Currently, each item is considered unique, which means that the item must be sold as a whole, and that multiple units of the item in stock must be treated as separate items in the ITEM table. The company’s management wants the design modified to include an inventory system that will allow multiple units of an item to be stored under one ItemID. The system should allow for a quantity on hand, a quantity on order, and an order due date. If the identical item is stocked by multiple vendors, the item should be orderable from any of these vendors. The SALE_ITEM table should then include Quantity and ExtendedPrice columns to allow for sales of multiple units of an item.

The company’s management has noticed that some of the fields in CUSTOMER and EMPLOYEE store similar data. Under the current system, when an employee buys something at the store, his or her data has to be reentered into the CUSTOMER table. The managers would like to have the CUSTOMER and EMPLOYEE tables redesigned using subtypes.

You are expected to perform the following:

  1. Draw an E-R data model for the company database schema. Use the IE Crow’s Foot E-R model for your E-R diagrams. Justify the cardinalities of each relationship. (12 points)
  2. Extend and modify the E-R data model by adding only the company’s inventory system requirements. Use the IE Crow’s Foot E-R model for your E-R diagrams. Create appropriate identifiers and attributes for each entity. Justify the decisions you make regarding minimum and maximum cardinalities. (4 points)
  3. Extend and modify the E-R data model by adding only the company’s need for more efficient storage of CUSTOMER and EMPLOYEE data. Use the IE Crow’s Foot E-R model for your E-R diagrams. Create appropriate identifiers and attributes for each entity. Justify the decisions you make regarding minimum and maximum cardinalities. (4 points)
  4. Combine the E-R data models from parts B and C to meet all the company’s new requirements, making additional modifications as needed. Use the IE Crow’s Foot E-R model for your E-R diagrams. (4 points)
  5. Describe how you would go about validating your data model in part D. (6 points)
  6. Convert the data model to a database design. Specify tables, primary keys, and foreign

    keys. (5 points)

  7. Describe how you have represented weak entities, if any exist. (2 points)
  8. Describe how you have represented supertype and subtype entities, if any exist. (3 points)

3

Related Posts