IDatabase Systems Posted: Nov 02, 2021 Due: 11:59PM , Nov 30th, 2021 Q1 (25 points): Write SQL code The following database will be used in this question: SALESREP CUSTOMER a) Write the standard SQL...

IDatabase Systems

Posted: Nov 02, 2021 Due: 11:59PM , Nov 30th, 2021

Q1 (25 points): Write SQL code

The following database will be used in this question:

SALESREP

CUSTOMER

a) Write the standard SQL query to retrieve all information about the customer whose customer number is 8590

b) Write the standard SQL query to retrieve the Name and PhoneNumber of customers with a balance greater than 50

Page 1 of 6

c) Write the standard SQL query to retrieve the Name and PhoneNumber of customers whose name begins with 'S'.

d) Write the standard SQL query to retrieve the average customer balance grouped by SalesRepNo

e) Write the standard SQL query to retrieve only the name of the sales representative and the name of the customer for each customer that has a balance greater than 400

Page 2 of 6

Q2 (30 points): Normalization

a) Put the following relation in BCNF:

STUDENT_ACTIVITY (StudentID, StudentName, Activity, ActivityFee, AmountPaid)

b) Put the following relation in BCNF:
Student(SSN, sName, address, HScode, HSname, HScity,GPA, priority)
Assuming a student can attend more than one high school, and priority is determined by GPA

Page 3 of 6

Q3 (25 Points): E-R Model:
Design an E-R model for garage repair shop based on the following order form:

Page 4 of 6

Q4 (20 points): Database Design

DEPARTMENT

DepartmentName

Phone Budget

EMPLOYEE

EmpName

Address Email Phone ReviewDate Hiredate EmpCode

1. 
   

   A) Convert this data model to a database design (visual representation).
   

   
   


2. 
   

   B) Specify primary keys, foreign keys, and alternative keys
   

   
   


3. 
   

   C) Specify data types
   

   
   


4. 
   

   D) Specify Null/NOT NULL constraints
   

   
   


5. 
   

   E) Specify unique constraints.
   As a database designer, you need to decide wither to use surrogate keys or not.
   

   
   

Document your minimum cardinality enforcement using referential integrity actions for required parents, if any, and the for required children, if any.

if a department primary key is changed all children foreign key values are changed. A department can’t be deleted if it has employees belonging to it.

Page 5 of 6

Q5 (25 points): Database Implementation Using your answer to question 5:

1. 
   

   A) Write CREATE TABLE statements for each of the tables.
   

   
   


2. 
   

   B) Add a constraint to the EMPLOYEE table so HireDate is later than ReviewDate.
   

   
   


3. 
   

   C) Add a constraint to the EMPLOYEE table to make Phone an alternate key in EMPLOYEE.
   

   
   


4. 
   

   D) Write the needed SQL code to insert one row in each table (use any data values you prefer)
   

   
   


5. 
   

   E) Write the needed SQL code to change the phone number of the employee with EmpName “John Doe” to 360-
   

   
   

   287-8810.
   

   
   


6. 
   

   F) Write the needed SQL code to delete the rows for employees with last name 'Smith'.
   

   
   

Page 6 of 6