HIT234 Database Concepts Assignment

Question1:ERDiagrams(25marksintotal)

ForeachofthebelowscenariosdrawaseparateE-RDiagramincludingattributes,cardinalitiesand 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 departmentandzerotooneOneoftheteammemberssupervisestheotherteammembers on the project.
2. A company hasfourEachdepartmenthasonemanager.Eachdepartmentemploys staff. Each staff may work for one or more departments. Staff may be supervised by another staff at least.
3. Acarinsurancecompanywhosecustomersownoneormorecars each.Eachcarhasassociated with it zero to any number of recorded accidents.
4. Universityregistrarhas thefollowingentities:Courses(includingcoursenumber,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(identifiedbyEMP-ID,SURNAME,FIRST-NAME,andDate-ofBirth)makemany 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.

Question2:Relationships

Considerthefollowing3NFrelationsaboutasororityorfraternity: 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) (15marksintotal)

1. Show which attributesareforeignkeysandjustifyyour
2. Draw an E-Rdiagramfortheserelations,usingyouranswerinpart
3. Explaintheassumptionsyoumadeaboutcardinalitiesin youranswertopartExplainwhyitis said that the ER data model is more expressive or more semanticallyrich than the relational data model.

Question3:Normalization(25marksintotal)

1. Assume that atDarwinFurnitureproductsarecomprisedofcomponents, productsare assigned to salespersons, and components are produced by vendors.Also assume that in the relation PRODUCT(Prodname,Salesperson,Compname,Vendor)Vendorisfunctionallydependenton 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 theywritefora publisher.Theamount ofthe royaltycanvarybyauthor, bybook, and by the edition of the book. (15 marks)
3. Based on thedependencydiagram,createadatabasewhosetablesareatleastin2NF, showing the dependency diagram for each table.
4. Create a database whose tables are atleast in 3NF, showing the dependency diagram foreach table.

Question4:SQLstatements (20marksintotal)

Thefollowingistherelationalschemaofpartofthedatabasethat HighTechPtyLtd 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)

Usingtheaboveschema,writeSQLqueriesthatwillanswer/processthefollowing:

1. List all citieswherean employeeOnlyshowthecityonce.
2. What is thelowestsalarypaid foranyemployeethatlivesin thecityofDarwin?
3. For each qualification,showthetotalnumberofemployeesthathavethatShow qualification id, qualification and the total number.
4. List any employeeswhoareonannualleaveon01/05/2018.Note,dateformatis#01?05? 2018#. Show employee number only.
5. What is thetotalsalarycostincurredbyHighTechforemployeesintheMarketingdepartment?
6. List the employee(s)withthehighestShowemployeenumberandname.
7. List the employeenames andallof theinstitutionsthattheyhavestudied
8. How many employees haveno qualifications?
9. Delete all leaverowsiftheleave endedpriortothestartofthis
10. Increase the salaryof allemployeesby$80.

Question5:ViewsandGranting(15marksintotal)

UsingthetablesinAppendixA

1. What are theadvantagesofusingVIEWinSQL?
2. UsingstatementCreateaVIEWshowingthedetailsofallCustomerswhohavereturned an

1. CreateaqueryusingthenewviewtofindthecustomerinCoconutGroveor

1. AsanownerofthisviewGRANTpermissiontousers989232toenablehim/herfullaccess

Automobile

AppendixA

Reg No

CustomerTable

CustomerAutomobileTable