EXTRA CREDITS PROBLEMS

hw1.pdf

Home >Computer Science homework help >EXTRA CREDITS PROBLEMS

CSCI 6622 — Databases — 14 Winterl HW 1, Due: 01/29/14

• 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. Email submissions will not be accepted.

• A number of problems will ask you to submit some material (typically a database) on blackboard:

– Please figure out ahead of time how assignments can be submitted on blackboard.

– Please note that what is turned in on blackboard should exactly match what is being turned in on the hard copy. So, for example, if you have some queries on the database you are submitting on blackboard, those should be an exact match with what is being turned in on the hard copy.

– If the hard copy is submitted on time but the blackboard submission is done after the start of class on the due date, the assignment/problem will be marked late.

– If the the blackboard submission is done on time but hard copy is submitted after the start of class on the due date, the assignment/problem will be marked late.

• The extra credit problems are to be done on separate sheets of paper and to be handed in near the end of the trimester, exact date to be announced later.

• Quiz 1 (open book, open notes) will be on 02/05/14 and will cover topics from the following list: Chapter 1: Introduction. Chapter 3: Relational Model, Chapter 4: SQL DDL

• For Problem 2 and Problem 5, I have put on blackboard Premiere Products database as hw1database 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)

A university library database records information about books; for each book, it records the book isbn number (which is unique), and the book name. In addition, it records which books have been checked out : the information it keeps for the books which have been checked out is the student id of the student who checked it out, and the date on which the book was checked out. Some books may not be checked out. You can assume that there is only one copy of each book.

Each of the following situations concerns how we capture the information in different situations.

• Situation 1: For each book, we only keep track of the student who has currently checked out the book i.e. we don’t keep track of who checked out the book in the past.

• Situation 2: For each book, we keep track of the student who has currently checked out the book and we also keep track of who checked out the book in the past. Here you can assume that no student ever checks out the same book more than once.

• Situation 3: For each book, we keep track of the student who has currently checked out the book and we also keep track of who checked out the book in the past. Here it is possible that the same student has checked out the same book more than once; for example, once on June 4, 2013 and another time on July 19, 2013. You can assume that no student can check out the same book twice on the same day.

For each of the above situations:

(a) In a manner similar to Elmasri Figure 3.7 draw the relational schema for the database. Specify all primary keys (by underlining) and foreign keys (by drawing arrows) in your relational schema.

(b) In a manner similar to Elmasri Figure 3.6, draw a small relational instance. Make sure your relational instance is consistent with the primary keys and foreign keys which you have specified in the relational schema. Make sure your relational instance has enough content to help illustrate the relational schema and the specified constraints.

Note that:

• Each of the parts has to be done completely independently of the others. So, for example, when you are doing the second part, you should not assume that the assumptions from the first part are true.

• If you are making any additional assumptions, state them clearly. • Other than what has been specified, you don’t need to record any other information; for example, you don’t

need to record the name of the authors.

• You do not need to implement this database in Access or any other DBMS.

2. (15 points) You have to implement this using the Premier Products database. Copy hw1database (which is a ”clean” copy of the the Premier Products database ) into a database called HW1Q2. In this database, create a new table Cust2 which has two columns MyCustNumber, and MyRepNumber.

(a) The primary key should be MyCustNumber.

(b) MyCustNum will be a foreign key to CustomerNum in the Customer table.

(d) Enter the following information in the MyCustomer table:Enter the following information in the MyCus- tomer table: first row: MyCustNumber = 356 and MyRepNumber = 65; second row: MyCustNumber = 608 and MyRepNumber = 65.

(e) Show that the primary key constraint is being correctly enforced by trying to enter another row with the following information: MyCustNumber = 608 and MyRepNumber = 20.

(f) Show that the foreign key constraint is being correctly enforced by trying to enter another row with the following information: MyCustNumber = 148 and MyRepNumber = 75.

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 submit on blackboard the database HW1Q2 containing the new table Cust2 with the specified constraints and data you have entered, all the old tables of the Premier Products database and the Relationships involving the the new table Cust2 and the tables of the Premier Products database.

3. (40 points)

You have to build a database HW1Q3 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 620, 1, Fall 07, Green, another offering might be CS 620, 1, Fall 08, Green, another offering might be CS 620, 2, Fall 08, Purple, another offering might be CS 620, 3, Fall 08, 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 submit on blackboard the database HW1Q3 containing all the tables you have creaed with the specified constraints and data you have entered, and the Relationship involving the the new tables.

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 WORKS ON tuples with ESSN = ‘123456789’.

(b) Delete the PROJECT tuple with PNAME = ‘ProductY’.

(d) Insert Research, Chicago into DEPT LOCATIONS

(e) Insert ‘ProductB’,2,‘Bellaire’,4 into PROJECT.

5. (20 points) You have to implement this using the Premier Products database. Copy hw1database (which is a ”clean” copy of the the Premier Products database ) into a database called HW1Q5.

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 submit on blackboard the database HW1Q5 containing all the saved SQL queries (labelled query a, query b, query c), forms, reports, new tables, relationships etc. These should be clearly labelled so I can understand easily 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) In the Orders table, for OrderNum = 21610, change the CustomerNum to 148.

(b) Delete the row with OrderNum 21617 and PartNum CD52 from the OrderLine table.

(d) Create a form for the OrderLine table. Enter 3 more rows in the OrderLine 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 customer number for each rep number, grouped by rep number.

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