A HOMEWORK IN DATABASE

hw1_lp1_1.pdf

Home >Computer Science homework help >A HOMEWORK IN DATABASE

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.

(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.

3. (40 points)

You have to build a database for an university that is used to keep track of students’ transcripts. This is similar but not identical to the student database discussed in class.

• The university keeps track of each student’s name, student id number, major department, minor department (if any). The student id number has a unique value for each student.

• Each department is described by a name, department code, office phone number. Both name and code have unique values for each department.

• Each course has a course name, course number, number of credit hours, and offering department. The value of course number is unique for each course.

• Each offering of a course has a course number, section number, semester, instructor. The section numbers distinguishes different sections of the same course that are taught during the same semester; its values are 1, 2, 3, ...; up to the number of sections taught during each semester. For example, one offering might be CS 2215, 1, Fall 15, Green, another offering might be CS 2215, 1, Spring 16, Green, another offering might be CS 2215, 2, Fall 15, Purple, another offering might be CS 2215, 3, Fall 15, Purple. Note that there is only one instructor for a particular offering i.e. no ”co-teaching.”

• A grade report has a student number, information about the course and section and semester, letter grade, ( F, D, C, B, A).

(a) In a manner similar to Elmasri Figure 3.7, draw the relational schema for the database described.

(b) Build this database with the different tables in either Microsoft Access or another DBMS of your choice.

(d) In a manner similar to Elmasri Figure 3.6, create a small relational instance by populating the tables you have created. Make sure your relational instance is consistent with the primary keys and foreign keys which you have specified in the relational schema. Also make sure your relational instance has enough content to help illustrate the relational schema and all the specified constraints; every table should have at least 4 rows.

(e) If you are making any additional assumptions, state them clearly.

You need to turn in the following:

• You need to turn in a hard copy (paper copy) of the following: – In a manner similar to Elmasri Figure 3.7, your relational schema for the database described. Specify

all primary keys (by underlining) and foreign keys (by drawing arrows) in your relational schema.

– Print the foreign key connections by going to Relationships and then printing in the larger window.

– Your relational instance (i.e. the actual populated tables). This should look somewhat similar to Elmasri Figure 3.6.

• 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.

4. (10 points) The following problem is based on the Elmasri Company database, with all the constraints described in Figure 4.1. Now suppose we were working with the relational instance described in Figure 3.6. For the following operations, write down all the integrity constraints, if any, that are violated: No explanation necessary: all you have to do is to write down one or more of the following integrity constraints which have been violated: domain constraint, key constraint, entity integrity constraint, referential integrity constraint

(a) Delete the WORKSON tuples with ESSN = ‘333445555’.

(b) Insert ‘ProductA’, 4, ‘Bellaire’, 2 into PROJECT.

(d) Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN = ‘999887777’ to ‘943775543’.

(e) Delete the PROJECT tuple with PNAME = ‘ProductX’.

5. (20 points) You have to implement these related to the Premiere Products database.

You need to turn in the following:

• You need to turn in a hard copy (paper copy) of the following: – ∗ it should be clearly stated (in English) what is the data the query is retrieving: for (a),(b),(c) you

need to state the query, while for (d) and (e) you need to state what the form and report are about.

∗ For (a),(b),(c), show the SQL to implement the query. ∗ For (a),(b),(c),(d), show what the relevant table looked like before the change was made, and then

show what the table looks like after the change has been made.

• 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

– The relational instance

– All the saved SQL queries, forms, reports, new tables, relationships etc. These should be clearly labelled so I can understand what goes with what.

Notes:

• For (a), (b), (c), feel free to use Access QBE (query by example) if you find it easier to do so, though what you have to show is the SQL: if you use QBE, you can go to the SQL view and find the equivalent SQL query generated by Access.

(a) Delete the part with PartNum “FD21” from the Part table.

(b) Insert a row in the Orders table with values 32000, “9/15/2015”, 282.

(d) Create a form for the Parts table. Enter 3 more rows in the Parts table using the form your have created. For this query, you have to also give a printout showing what the form looks like i.e. what would it look like to the user who is using the form to enter rows into the Orders table.

(e) You have to print out a report containing a list of all the order number for each customer number, grouped by customer numbers.

Extra Credit 1: In the SQL given to create the DEPARTMENT table in the Elmasri Company Database in Figure 4.1 is as follows:

CREATE TABLE DEPT (

DNAME VARCHAR(10) NOT NULL,

DNUMBER INTEGER NOT NULL,

MGRSSN CHAR(9) NOT NULL,

MGRSTARTDATE CHAR(9),

PRIMARY KEY (DNUMBER),

UNIQUE (DNAME),

FOREIGN KEY (MGRSSN) REFERENCES EMP (SSN) );

Having the MGRSSN specified as NOT NULL enforces the constraint that every department has to have a manager. Now suppose we were to look at an alternative way of capturing the department information by having two tables: one table DEPT2 for the department information, and another table MANAGES for the manager information as follows:

CREATE TABLE DEPT2 (

DNAME VARCHAR(10) NOT NULL,

DNUMBER INTEGER NOT NULL,

PRIMARY KEY (DNUMBER),

UNIQUE (DNAME));

CREATE TABLE MANAGES (

DNUMBER INTEGER NOT NULL,

MGRSSN CHAR(9) NOT NULL,

MGRSTARTDATE CHAR(9),

PRIMARY KEY (DNUMBER),

FOREIGN KEY (MGRSSN) REFERENCES EMP (SSN) );

Does having MGRSSN be NOT NULL in the MANAGES table enforce the constraint that every department has to have a manager ?

1. Give a YES/NO answer.

2. Explain your answer.

Extra Credit 2: In the language of your choice write a program which will take as input the SQL DDL for a couple of tables and the corresponding relational instances, and check whether the relational instances satisfy all the constraints (primary key, unique, not null, foreign key) specified in the SQL. Show how your program works on a couple of simple examples. What you need to turn in is a printout of the source code, the disk containing the program, and printout of the sample runs.

Extra Credit 3: In the language of your choice write a program which will take as input a simple SQL statement (SELECT attributename FROM tablename) and the table i.e. the relational instance. The output of your program should be the table that would have been output by the SQL statement. So what you have to do is to write a “compiler” type of program which will implement a SQL statement. What you need to turn in is a printout of the source code, the disk containing the program, and printout of a sample run (i.e. the SQL statment, the relational instance, the output table).