Database homework

CSCI 2215 — Databases — Fall 2015 HW 1, Due: Friday, 09/25/15 L. Page • Please read the policies on plagiarism and on homework guidelines (in the course outline handed out on the first day) and remember that I enforce these policies. • The extra credit problems are to be done on separate sheets of paper and can be handed in up to the day of the final exam. • Quiz 1 (open book, open notes) will be on the following week, on Thursday,Oct. 1 in the last 45 minutes of class and will cover topics from the following list: Chapter 1: Introduction. Chapter 3: Relational Model, Chapter 4: SQL DDL • Email submissions will not be accepted. • You will need to create a gmail or Yahoo account to email Access Files to me. You will need to send me your databases for HW1, HW2, and the course project to [email protected] • For Problem 2 and Problem 5, I have put on blackboard Premiere Products database as ”hw1database LP” which you need to use for this problem; do not use the Premiere Products database inclass1 since that has been modified from the original. • For Problem 2 and Problem 5, you can do the implementation in either Microsoft Access or another relational DBMS of your choice. If you are doing this in another DBMS, it is your responsibility to make sure you have the same tables, by either entering the same data yourself or by transforming the Access data. Also, if you are using another DBMS, please indicate clearly which DBMS you are using.
1. (15 points) Consider the following six relations for an order-processing database application in a company: CUSTOMER (CustId, Cname, City) ORDER (OrderId, OrdDate, CustId, OrdAmt) ORDERITEM (OrderId, ItemId, Qty) ITEM (ItemId, UnitPrice) SHIPMENT (OrderId, WarehouseId, ShipDate) WAREHOUSE (WarehouseId, City) OrdAmt refers to total dollar amount of an order; OrdDate is the date the order was placed; ShipDate is the date an order is shipped from the warehouse. A single order can be shipped from several warehouses. In a manner similar to Elmasri Figure 3.7, specify the primary keys (by underlining) and the foreign keys (by drawing arrows) in this relational schema.
2. (15 points) You have to implement this using the Premier Products database. Create a new table MyCustomer which has two columns MyCustNum, and MyRepNum.
(a) The primary key should be MyCustNum. (b) MyCustNum will be a foreign key to CustomerNum in the Customer table. (c) MyRepNum will be a foreign key to RepNum in the Rep table. (d) Enter the following information in the MyCustomer table: first row: MyCustNum = 462 and MyRepNum = 65; second row: MyCustNum = 408 and MyRepNum = 35 (e) Show that the primary key constraint is being correctly enforced by trying to enter another row with the following information: MyCustNum = 462 and MyRepNum = 35 (f) Show that the foreign key constraint is being correctly enforced by trying to enter another row with the following information: MyCustNum = 325 and MyRepNum = 40
You need to turn in the following: • You need to turn in a hard copy (paper copy) of the following: – Print the foreign key connections by going to Relationships and then printing in the larger window. – The relational instance (i.e. the actual populated tables) for the new table that you have created. – Show how the DBMS is able to stop the primary key and foreign key violations. • You also need to turn in a CD-ROM or email a zip file to [email protected] containing:
– The CD-ROM should be clearly labelled with your name and the name of the DBMS you are using. – The zip file should be named: HW1.YOUR NAME.zip for example I would send in HW1.Liberty Page.zip – All the tables with the specified constraints. – The relational instance. – Any saved SQL queries, clearly labelled so I can figure out what they are.