HIT234 – Database Concepts Assignment

Question 1: ER Diagrams (25 marks in total)

 For each of the below scenarios draw a separate E-R Diagram including attributes, cardinalities and identifiers when applicable.

1. DarwinCom Pty Ltd is made up of a number of departments that manage none or more projects. Each project is made up of none or more team members. Each team member belongs to one department and zero to one  One of the team members supervises the other team members on the project.
2. A company has four  Each department has one manager. Each department employs staff. Each staff may work for one or more departments. Staff may be supervised by another staff at least.
3. Acar insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents.
4. University registrar has the following entities: Courses (including course number, title, credits, syllabus, and prerequisites); Course offerings, (including course number, year, teaching period, instructors, timings and classroom); Students (including student-id, name, and program); and Instructors (including identification number, name, department, and title). The enrolment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modelled. An instructor could teach in only one course. Each course only runs in one session
5. Employees(identified by EMP-ID, SURNAME, FIRST-NAME, and Date-of Birth) make many orders (identified by ORDER-NO, ORDER-DATE, DES, WUOTED-PRICE) for Customers (Identified by CUST-ID). The orders are for one customer at a time but a customer might have many orders. The orders create many requests (Identified by REQUEST-NO, START-DATE, END-DATE). Those requests might result in few jobs (identified by JOB-NO, COST) and consequently will use different materials (identified by MATERIAL-ID, MATERIAL-DES). The requests are made to one section but a section has many requests.

 Question 2: Relationships 

Consider the following 3NF relations about a sorority or fraternity: MEMBER (Member_ID, Name, Address, Dues_Owed)

OFFICE (Office_Name, Officer_ID, Term_Start_Date, Budget) EXPENSE (Ledger_Number, Office_Name, Expense_Date, Amt_Owed) PAYMENT (Check_Number, Expense_Ledger_Number, Amt_Paid) RECEIPT (Member_ID, Receipt_Date, Dues_Received)

COMMITTEE (Committee_ID, Officer_in_Charge) WORKERS (Committee_ID, Member_ID)     (15 marks in total)

1. Show which attributes are foreign keys and justify your 
2. Draw an E-R diagram for these relations, using your answer in part 
3. Explainthe assumptions you made about cardinalities in your answer to part  Explain why it is said that the ER data model is more expressive or more semantically rich than the relational data model. 

Question 3: Normalization (25 marks in total) 

1. Assume that at Darwin Furniture products are comprised of components, products are assigned to salespersons, and components are produced by vendors. Also assume that in the relation PRODUCT(Prodname, Salesperson, Compname, Vendor) Vendor is functionally dependent on Compname, and Compname is functionally dependent on Prodname. Eliminate the transitive dependency in this relation and form 3NF relations. (10 marks)
2. The dependency diagram in the Figure below indicates that authors are paid royalties for each book that they write for a publisher. The amount of the royalty can vary by author, by book, and by the edition of the book. (15 marks)
3. Based on the dependency diagram, create a database whose tables are at least in 2NF, showing the dependency diagram for each table.
4. Create a database whose tables are at least in 3NF, showing the dependency diagram for each table.

 Question 4: SQL statements   (20 marks in total)

 The following is the relational schema of part of the database that HighTech Pty Ltd recently implemented:

DEPARTMENT (DeptNo, Name, Location)

EMPLOYEE (EmpNo, Name, Street, City, Postcode, HireDate, Salary, DeptNo) LEAVE (EmpNo, FromDate, ToDate)

EMPQUAL (EmpNo, QualID)

QUALIFICATION (QualID, Qualification, Institution)

Using the above schema, write SQL queries that will answer/process the following: 

1. List all cities where an employee  Only show the city once.
2. What is the lowest salary paid for any employee that lives in the city of Darwin?
3. For each qualification, show the total number of employees that have that  Show qualification id, qualification and the total number.
4. List any employees who are on annual leave on 01/05/2018. Note, date format is #01?05? 2018#. Show employee number only.
5. What is the total salary cost incurred by HighTech for employees in the Marketing department?
6. List the employee(s) with the highest  Show employee number and name.
7. List the employee names and all of the institutions that they have studied 
8. How many employees have no qualifications?
9. Delete all leave rows if the leave ended prior to the start of this 
10. Increase the salary of all employees by $80.

 Question 5: Views and Granting  (15 marks in total)

 Using the tables in Appendix A

1. What are the advantages of using VIEW in SQL?
2. Using statement Create a VIEW showing the details of all Customers who have returned an

1. Createa query using the new view to find the customer in Coconut Grove or

1. Asan owner of this view GRANT permission to user s989232 to enable him/her full access

Automobile

Appendix A

Reg No

 Customer Table

Customer Automobile Table