Understanding ER Diagrams and Relational Models in Databases
School
City University of Hong Kong**We aren't endorsed by this school
Course
COMPUTER S 3402
Subject
Computer Science
Date
Dec 11, 2024
Pages
12
Uploaded by GrandSummerHeron25
1CITY UNIVERSITY OF HONG KONG Course code & title : CS3402 Database Systems Session : Semester A 2024/25 Time allowed : 2 Hours This paper has 12 pages (including this cover page). 1.This paper consists of FIVE questions. 2.Write down your answer in the space provided. This is an open-book examination. STUDENT ID VENUE NAME SEAT NO Q1 (20%) Q2 (20%) Q3 (20%) Q4 (20%) Q5 (20%) Total (100%) Candidates are allowed to use the following materials/aids: Printed lecture notes, personal notes, textbook and other course handout materials. Materials/aids other than those stated above are not permitted. No Electronic devices.
2 Problem ONE: ER Diagram [20 points] Consider a university research project management system consisting of the following entities: (a) Researcher, having a unique ORCID, and other attributes: name, sex, titles. (b) Department, having a unique department name, and other attributes: department location, total number of researchers belongs to the department. (c) Publication, having a unique DOI, and other attributes: title, date of publication, journal/conference name. (d) Project, with attributes: project title, start date, end date, duration of the project. 1.Draw the ER diagram for the entity type Department. [3 points] Answer: 2.Draw the ER diagram for the entity type Researcher, assume each Researcher could have multiple different titles, and each title is composed by the title name and the award year. [4 points] Answer: 3.Assume each Researcher only work for one Department, and each Department has many Researchers, among them one Researcher works as the head of the department. A Researcher can publish no or many Publications, and each Publication could have one or multiple authors (Researchers). Additionally, a Publication could cite or be cited by no or many Publications. Draw the ER diagram
3 for relationships between Researcher, Departmentand Publication.(No need to draw the attributes of each entity). [8 points] Answer: 4. Assume each Project is led by one Researcher, and a Researcher can lead no or many Projects. Different Researchers may have Projects with the same title, but the same Researcher will not have two Projects with the same title. Draw the ER diagram for the entity type Projectand its relationship to the entity type Researcher. (No need to draw the attributes of Researcher.) [5 points] Answer: Problem TWO: Relational Model [20 points] 1.Please convert the following completed ER diagram into Relational Schema. [7 points]
4 Note: you can define a relation in the sample format below: Answer:
5 2.How many primary keys, candidate keys, and superkeys are there for the relation 'Branch’? [4 points] Answer: It has 1 primary key (1 points), 2 candidate key (1 points) and 12 superkeys (2 points) 3.Assuming that the tables for the entities 'Bank,' 'Branch,' 'Loan,' 'Customer,' and 'Library' already exist, please create necessary tables to represent the entity 'Account' and the relationship 'Apply' using SQL statements, while defining the primary keys and foreign keys. (Hint: you can define the datatype of attributes by yourself). [9 points] Answer: CREATE TABLE Account ( Account_NO INT, Type CHAR(10), Balance REAL, Customer_ID INT, Branch_ID INT, PRIMARY KEY(Account_NO), FOREIGN KEY (Customer_ID) REFERENCE Customer (Customer_ID), FOREIGN KEY (Branch_ID) REFERENCE Branch (Branch_ID) ); CREATE TABLE Transaction
6 ( Account_NO INT, Time TIME, Type CHAR(10), Amount REAL, PRIMARY KEY(Account_NO, Time), FOREIGN KEY (Account_NO) REFERENCE Account(Account_NO), ); CREATE TABLE Apply ( Loan_ID INT, Customer_ID INT, PRIMARY KEY(Loan_ID, Customer_ID), FOREIGN KEY (Loan_ID) REFERENCE Loan (Loan_ID), FOREIGN KEY (Customer_ID) REFERENCE Customer (Customer_ID) ); Problem Three: Integrity Constraints [20 points] 1 Suppose we have a relational database of a Bookstore system which describes the orders of books made by customers. It contains three tables: Customer, BookOrder, and Book. The current state of the database is shown in the following tables. [15 points] Customer Customer_idNameGender Birth_date Address 1 Alice Johnson Female 1999-01-05 25 Sunset Ave., Bridgewater 2 Robert Davis Male 1999-01-05 17 Elm St., Plainview 3 Alice Johnson Female 1995-03-04 17 Elm St., Plainview BookOrder Order_idCustomer_idBook_id Quantity 101 1 1001 2 102 3 1002 5 103 2 1001 1 104 1 1003 1 105 3 1002 4 Book
7 Book_idTitleAuthor Price 1001 "Data Science 101" John Smith $35.99 1002 "AI for Everyone" Andrew Ng $35.99 1003 "AI for Everyone" David Garcia $42.00 1004 "Data Science 101 - 2nd Edition" John Smith $38.99 (1) Analyze the primary keys of each table based on the application. [3 points] Answer: For the Customer table: Customer_id For the BookOrder table: Order_id For the Book table For the Book table: (Book_id, Title) (2) Suppose all three tables are already created, write corresponding SQL statements to define all foreign keys. [4 points] ALTER TABLE BookOrder ADD CONSTRAINT FK_bookorder_customer FOREIGN KEY Customer_id REFERENCES Customer(Customer_id); ALTER TABLE BookOrder ADD CONSTRAINT FK_bookorder_book FOREIGN KEY Book_id REFERENCES Book(Book_id); (3) For 3.1 and 3.2 below, suppose each of the following operations is applied directly to the database. Discuss all integrity constraints violated by each operation if any, and the different ways of enforcing these constraints. 3.1) Delete tuple <1002, "AI for Everyone", Andrew Ng, $35.99> from Book table. [4 points] Answer: Violates the referential integrity constraint because multiple existing tuples in BookOrder refer to this book tuple. If the tuple is deleted, these BookOrder entries will refer to a non-existent book. We may enforce the constraint by: (i) rejecting the deletion operation or (ii) cascading deletion, removing all BookOrder tuples that refer to the deleted Book tuple. 3.2) Insert <null, ‘Charlie’, ‘Male’, 1989, ‘452 Maple St. Plainview’> into Customer. [4 points]
8 Answer: Violates the entity integrity constraint because Customer_id is a primary key and cannot be NULL. Violates the domain constraint because Birth_date is not stored in a valid date format; it should not be an integer. We may enforce the constraint by: (i) Rejecting the insertion, or (ii) Correcting the values by providing a valid Customer_id and ensuring the Birth_date follows the correct format (e.g., Date 'YYYY-MM-DD'). 2Given a relation schema R (A,B,C,D,E) with the function dependency set F={ AB→D，AC→B, D→E, E→A}, please determine whether each of the following functional dependency is in F+. (Hint: no need to show the proof.) [5 points] 1)BE→D2)AC→E3)BC→D4)DE→B 5)AC→D Answer: 1) 2) 5) are in the F+ but 3) 4) are not in the F+. Problem Four: Normalization [20 points] 1. Suppose we have a relation R with attributes A, B, C, D, E, F, G, H and the functional dependencies are: A→B, B→E, C→HD, BG→F. Please prove that FD: ACG→EH holds. [5 points] Answer: 1.AB (given) 2.BE (given) 3.AE (transitivity by 1&2) 4.CHD (given) 5.CH (decomposition rule by 4) 6.AC EC (augmentation rule by 3) 7.EC EH (augmentation rule by 5) 8.AC EH (transitivity by 6&7) 9.ACG AC (reflective rule by 8) 10.ACG EH (transitivity by 8&9)
9 2. Let’s consider the following relationship R storing the information about concert. R(ConcertID, VenueNo, VenueName, Performer, ConcertDate, StageNo, LocationID, StageCapacity, ManagerNo) It has following functional dependencies: ConcertID → VenueNo VenueNo → VenueName {ConcertID, Performer, ConcertDate} → {StageNo, LocationID, ManagerNo} {StageNo, LocationID, ConcertDate} → StageCapacity (1) Identify all the candidate keys in this table. [2 Points] Answer: {ConcertID, Performer, ConcertDate} (2) Is the relation R in 2NF and why? If not, decompose it into Two tables which satisfy 2NF. [5 Points] Answer: Not in 2NF, because there exists partial function dependency on primary key, ConcertID → VenueNo and VenueNo → VenueName 2NF decomposition: R1 (ConcertID, VenueNo, VenueName) R2 (ConcertID, Performer, ConcertDate, StageNo, LocationID, ManagerNo, StageCapacity) (3) Does your decomposition in (2) satisfy 3NF and why? If not, normalize it into 3NF. [5 Points] Answer: Not in 3NF, because there exists transitive function dependency on primary key: ConcertID → VenueNo → VenueName in R1 and {StageNo, LocationID, ConcertDate} → StageCapacity in R2. 3NF decomposition: R1A (ConcertID, VenueNo) R1B (VenueNo, VenueName) R2A (ConcertID, Performer, ConcertDate, StageNo, LocationID, ManagerNo) R2B (StageNo, LocationID, ConcertDate, StageCapacity) (4) Does your decomposition in (3) satisfy BCNF and why? If not, normalize it into BCNF. [3 Points] Answer: Yes, it already satisfies BCNF. Because in each table, for each functional dependency, the left-hand side is a super key. Problem FIVE: SQL Given the following relations about the information of an online shopping system. [20 points] Customer (CustomerID: integer, Name: string, Age: integer, City: string) // describes the customers including ID, name, age, and city. Product (ProductID: integer, Name: string, Category: string, Price: float)
10 // describes the products including ID, name, category, and price. Order (OrderID: integer, CustomerID: integer, OrderDate: date) // describes the orders placed by customers. OrderItem (OrderID: integer, ProductID: integer, Quantity: integer) // describes the items in each order, including which products and quantities. Suppose now we have a valid database state. Answer the following questions by completing the missing parts of the given SQL statements. (1)List the Name and Price of all products in the 'Electronics'category that cost more than 500. [4 points] SELECT _______________________________________________________ FROM _________________________________________________________ WHERE ________________________________________________________; Answer: SELECT Name, Price FROM Product WHERE Category = 'Electronics' AND Price > 500; (2)List the CustomerID and Name of customers who have ordered products from both 'Electronics'category and 'Books'category. [4 points] (SELECT _______________________________________________________ FROM _________________________________________________________ WHERE ________________________________________________________ ) ________________________________________________________________ (SELECT _______________________________________________________ FROM _________________________________________________________ WHERE ________________________________________________________); Answer: (SELECT DISTINCT C.CustomerID, C.Name FROM Customer AS C, Order AS O, OrderItem AS OI, Product AS P WHERE C.CustomerID = O.CustomerID AND O.OrderID = OI.OrderID AND OI.ProductID = P.ProductID AND P.Category = 'Electronics') INTERSECT (SELECT DISTINCT C.CustomerID, C.Name FROM Customer AS C, Order AS O, OrderItem AS OI, Product AS P WHERE C.CustomerID = O.CustomerID AND O.OrderID = OI.OrderID AND OI.ProductID
11 = P.ProductID AND P.Category = 'Books'); (3)List the ProductID and Name of products whose names contain the substring 'pro'and are priced below 1000. [4 points] SELECT _______________________________________________________ FROM _________________________________________________________ WHERE _______________________________________________________; Answer: SELECT ProductID, Name FROM Product WHERE Name LIKE '%pro%' AND Price < 1000; (4)Create a view called CustomerOrdersthat contains the CustomerID, CustomerName, and OrderID for all orders placed by customers from 'New York'. [4 points] ___________________ CustomerOrders (CustomerID, CustomerName, OrderID) _________________________________________________________________ FROM ___________________________________________________________ WHERE __________________________________________________________; Answer: CREATE VIEW CustomerOrders (CustomerID, CustomerName, OrderID) AS SELECT C.CustomerID, C.Name, O.OrderID FROM Customer AS C, Order AS O WHERE C.CustomerID = O.CustomerID AND C.City = 'New York'; (5)List the ID of products that have NOT been ordered by any customer from 'Los Angeles,' order results by product ID in descending order. [4 points] (SELECT FROM _________________________________________________________)_________________________________________________________________(SELECT DISTINCT OI.ProductID FROM OrderItem AS OI, __________________________________________ WHERE ________________________________________________________) ________________________________________________________________;Answer: (SELECT DISTINCT ProductID FROM Product) EXCEPT (SELECT DISTINCT OI.ProductID FROM OrderItem AS OI, Order AS O, Customer AS C WHERE OI.OrderID = O.OrderID AND O.CustomerID = C.CustomerID AND C.City = 'Los
12 Angeles') ORDER BY ProductID DESC;