School of Business I.T. Semester 1 - 2017 Business Data Management and Analytics Page 1 of 5 SCHOOL OF BUSINESS INFORMATION TECHNOLOGY Business Data Management and Analytics Tutorial 6 - Data Modelling – ERD  Relational Task 1 – translating ERD to Relational Draw a relational model for the following HOSPITAL E-R Diagram: TREATMENT BED PHYSICIAN PROCEDURE PATIENT Name Procedure No Bed No Floor No Shared Patient No Name Address DOB Date admitted Date discharged Treatment No Side Effects Survival Rate Scheduled Fee Doctor No Name Qualification OF IN BY FOR Description School of Business I.T. Semester 1 - 2017 Business Data Management and Analytics Page 2 of 5 Task 2 – Translating Relational to ERD Draw E-R diagram (including attributes) for the following FINANCIAL relational model: CUSTOMER ACCOUNT TRANSACTION Customer_No Account_no Transaction_no Customer_name Customer_no Account_no Customer_address Account_Balance Transaction_Date Date_of_birth Date_Account_opened Transaction_Type (Deposit or withdrawal) Transaction_Amount School of Business I.T. Semester 1 - 2017 Business Data Management and Analytics Page 3 of 5 Task 3 – Business Case Model 1 Harry Happy is a used car dealer and runs one of the most profitable car yards in Melbourne. Harry has asked you to design a database for his office computer so he can record details of his sales. Harry has given you a copy of his current paper invoice to use as a guide: HARRY'S USED CARS INVOICE Invoice No: __________ Date: __________ SOLD TO: Name: Street: Suburb: State: ____________________________________________ ____________________________________________ ____________________________________________ ______________________ PostCode: ___________ Telephone: (____) _____ - ___________ SOLD BY: _____________________________________________________ TOTAL PRICE: ____________________ Make Model Year Serial Number Colour • Produce an E-R diagram and a relational model of your design. • Also state any assumptions you have made, and any major business rules that may apply to the system. HOMEWORK EXERCISES The following two pages contain homework exercises 1 and 2. You are required to complete these exercises and have them ready at the beginning of tutorial 7 for assessment. Your answers will be assessed by your tutor during the tutorial class and you will be given both feedback and a mark. The exercises, combined, are worth 1 mark. School of Business I.T. Semester 1 - 2017 Business Data Management and Analytics Page 4 of 5 Homework 1 – Translating ERD to Relational Draw a relational model for the following MUSIC COLLECTION E-R Diagram: SONG ALBUM ARTIST belongs CATEGORY Album_no name Artist_no name type (B or I) gender name Song_no duration writer Category_no name label sequence date_of_birth belongs RECORDING School of Business I.T. Semester 1 - 2017 Business Data Management and Analytics Page 5 of 5 Homework 2 – translating Relational to ERD Draw an E-R diagram (including attributes) for the following PURCHASING relational model: Customer(Customer ID, Tax ID, Name, Address, City, State, Zip, Phone) Order(Order No, Customer ID, Date Placed, Date Promised, Terms, Status) Order Line(Order Line No, Order No, Product Code, Qty) Product(Product Code, Product Description, Price)