Posted: September 5th, 2023

DATABASE ASSESSMENT Part 1 – E-R DIAGRAM

DATABASE ASSESSMENT Part 1 –
E-R DIAGRAM
The management of Santos Tour Down Under decided to replace the spreadsheet with a database. You were asked to create the new database. Your first step is to create the database model using the E-R Diagram. Note that this is part of your assessment.
Instructions:
1. Read the Database Assessment Case Study Background (Database Case Study Background.docx)
2. Identify the entities and attributes of the database module using the following information:
a. Santos Tour Down Under must maintain information about its sales reps, customers, product inventory, order.
b. For the sales rep, the sales rep number, last name, first name, street address, city, state, zip, country total commission , and commission rate for each sales rep must be recorded
c. Customer information must also be maintained including the customer number, name, street address, city, state, zip, country, current balance, and credit limit for each customer, as well as the number of the sale rep who represents the customer.
d. The product id, product name, product colour, quantity available, its manufacturer id and unit price are also maintained.
e. For each order, the order number, date order was placed, and the customer number that placed the order must also be stored.
3. Identify the relationships among entities using the following information:
a. A customer can only have one sales representative but a sales representative can have one or more customers.
b. A customer can have zero or more orders and each order has one customer.
c. An order can have several products and a product can have several orders.
d. Given the order number and the product ID, the quantity of products ordered and the quoted price is stored (Hint: these attributes should be added once the many-t-many relationship between order and product is resolved, the new entity should be called orderline).
4. Create the database model using E-R Diagram. You can use draw.io or any of the E-R diagram drawing tools you prefer.
5. Save your E-R Diagram.
6. Convert the E-R Diagram to relational schema (relational model).
7. Copy your E-R Diagram and save your relational schema in a document.
8. Save your document.
9. BACK-UP! BACK-UP! BACK-UP! Do not forget to keep a back-up files. Either upload in your google drive or dropbox; and/or just e-mail the file to yourself so that you have a copy in your email; and/or save in a USB drive.
10. You can show your work to your lecturer for feedback.
11. REMEMBER THAT THIS EXERCISE IS PART OF THE FINAL DATABASE ASSESSMENT YOU HAVE TO SUBMIT ON WEEK 12.
DATABASE ASSESSMENT Part 3 –
DATABASE QUERIES AND FORMS
You now have to create queries and reports using the database you created. Note that this is part of your assessment.
Instructions:
1. Use the database you created in the database assessment part 1 and part 2.
2. Create queries answering the following questions (note that you can use query wizard but there are some questions that need SQL statements to be written using the sql view:
a. List all the Product ID and Product Names being sold
b. List all the customers number and name who ordered tshirts
c. List all the customer number and customer names and the names of their sales representative.
d. List the entire customer name and the products they ordered.
3. Create 2 reports:
a. Report containing all the information about the customer.
b. Report containing all the products ordered per customer.
4. Save your database.
5. BACK-UP! BACK-UP! BACK-UP! Do not forget to keep a back-up files. Either upload in your google drive or dropbox; and/or just e-mail the file to yourself so that you have a copy in your email; and/or save in a USB drive.
6. REMEMBER THAT THIS EXERCISE IS PART OF THE FINAL DATABASE ASSESSMENT YOU HAVE TO SUBMIT ON WEEK 12.
7. The following are the list of documents you need to submit in week 12:
a. Document containing the E-R diagram, relational schema and normalised schema
b. LibreOffice Base Database file (containing the tables, forms, queries and reports)
DATABASE ASSESSMENT Part 4 –
Impact of Technology
You will now discuss the impact of using technology applied to the Santos Tour Down Under case study. Note that this is part of your assessment.
Instructions:
1. Discuss how Business Information Systems are being used in the case study (go to their website: tourdownunder.com.au/ and analyse from there)
2. Discuss the cultural, security, ethical and privacy issues in using IS and IT
3. Save your document
4. BACK-UP! BACK-UP! BACK-UP! Do not forget to keep a back-up files. Either upload in your google drive or dropbox; and/or just e-mail the file to yourself so that you have a copy in your email; and/or save in a USB drive.
5. REMEMBER THAT THIS EXERCISE IS PART OF THE FINAL DATABASE ASSESSMENT YOU HAVE TO SUBMIT ON WEEK 12.
At this point, you should have all the materials you need to submit your database assessment. As stated in the assessment document, you are required to submit the following on week 12:
1) A word document containing:
a) Introduction – a summary of the case study
b) Database
• explanation of the database development activities
• E-R Diagrams created
• Database tables created using LibreOffice Base
• SQL queries and the reports generated by those queries
c) Impact of using technology (at least 500 Words)
? Discuss how Business Information Systems are being used in the case study (go to their website: tourdownunder.com.au/ and analyse from there)
? Discuss the cultural, security, ethical and privacy issues in using IS and IT
2) LibreOffice Base files used in creating the tables, queries and forms