diff_months: 14

HIT234 – Database Concepts Assignment

Download Solution Now
Added on: 2023-04-19 04:58:34
Order Code: 488976
Question Task Id: 0
  • Subject Code :

    HIT234

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 (EmpNoQualID)

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

 

 

FirstName

 

LastName

 

Suburb

 

Reg No

 

Make

 

Year

 

Date Taken

Date Returned

Mohamed

Griffin

Nightcliff

BRG446

Ford

2009

15/11/2011

 

Anna

Thanh

Coconut Grove

NMT667

Madza

2010

16/11/2011

 

Justin

Brownworth

Nakara

CCT899

Toyota

2009

17/11/2011

 

 

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

 

 

FirstName

 

LastName

 

Suburb

 

Reg No

 

Make

 

Year

 

Date Taken

Date Returned

Anna

Thanh

Coconut Grove

NMT667

Mazda

2010

2/09/2011

3/09/2011

 

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

 

Automobile

Appendix A

Reg No

Make

Model

Year

Colour

BRG446

Ford

Meteor

2009

White

VRG655

Bmw

Coupe

2008

Blue

NMT667

Madza

Delivery Van

2010

Green

CCT899

Toyota

HiLuxe

2009

Red

FGR122

Mitsubishi

Magna

2007

Purple

 Customer Table

 

Employee_No

FirstName

LastName

Suburb

A118

Mohamed

Griffin

Nightcliff

A120

Ali

McDonald

Brinkin

A123

Albert

Underwood

Rapid Creek

A134

Sammy

Smith

Casuarina

A156

Anna

Thanh

Coconut Grove

A166

Justin

Brownworth

Nakara

 

 

Customer Automobile Table

 

Employee_veh

Reg No

Employee_No

Date Taken

Date Returned

B-12

BRG446

A118

1/09/2011

3/09/2011

B-13

NMT667

A156

2/09/2011

3/09/2011

B-14

CCT899

A166

2/09/2011

4/09/2011

B-15

FGR122

A134

3/09/2011

5/09/2011

B-16

BRG446

A118

4/09/2011

10/09/2011

B-17

NMT667

A166

5/09/2011

10/09/2011

B-18

NMT667

A134

11/09/2011

15/09/2011

B-19

FGR122

A166

12/09/2011

15/11/2011

B-20

NMT667

A118

14/11/2011

15/11/2011

B-12

BRG446

A118

15/11/2011

 

B-13

NMT667

A156

16/11/2011

 

B-14

CCT899

A166

17/11/2011

 

 

  • Uploaded By : Katthy Wills
  • Posted on : April 19th, 2023
  • Downloads : 0
  • Views : 146

Download Solution Now

Can't find what you're looking for?

Whatsapp Tap to ChatGet instant assistance

Choose a Plan

Premium

80 USD
  • All in Gold, plus:
  • 30-minute live one-to-one session with an expert
    • Understanding Marking Rubric
    • Understanding task requirements
    • Structuring & Formatting
    • Referencing & Citing
Most
Popular

Gold

30 50 USD
  • Get the Full Used Solution
    (Solution is already submitted and 100% plagiarised.
    Can only be used for reference purposes)
Save 33%

Silver

20 USD
  • Journals
  • Peer-Reviewed Articles
  • Books
  • Various other Data Sources – ProQuest, Informit, Scopus, Academic Search Complete, EBSCO, Exerpta Medica Database, and more