design a database management system for a fictional business. You will write a report which must...

Transcribed Image Text:

design a database management system for a fictional business. You will write a report which must propose an entity relationship diagram (ERD) model and subsequent logical design. The physical database implementation for the proposed model should be developed with MySQL. You will also present your design to the class. The report is worth 20%. You will also present your design to the class. The presentation is worth 10% Due date Word limit Other requirements Submission format 11.55pm Friday, Week 10 (11.55pm 11 Aug 2023) 2,500 Use 12 pt font Double-space your document to allow room for feedback State all your names and student numbers in the document header State the word count on the cover page of your report Include a bibliography at the end of your report APA referencing style Complete and submit a peer and self-evaluation form PDF file (.pdf) or Word (.doc) only for the project report PDF file (.pdf) or Word (.doc) only for the peer and self-evaluation form PDF file (.pdf) or PowerPoint (.ppxt) only for the presentation SQL statements in a text file or as SQL script (.sql) Business Problem Vintage Cellar (VC) is a small liquor retailer based in Darwin that sells both alcoholic and non- alcoholic beverages to its customers. The business has seen tremendous growth despite being in the infant stage as the business began trading only 7 months ago. VC recently approached your IT consulting firm, Darwin IT Consulting, and enquired about implementing a database solution. Vintage Cellar is keen to implement the database solution for three main functions, namely Inventory Management, Human Resource Management, and Supplier Relationship Management. After the initial meeting with VC, your team noted down several business rules for each of the three functions previously mentioned: VC stores information on all products that VC currently stocks and sells. The business stocks and sells both Alcoholic and Non-alcoholic beverages. Within the Alcoholic beverage category, there are 3 major sub-categories, namely Wine, Spirits and Beers. An employee may be employed on a Full-time, Part-time or Casual basis. Casual employees receive 25% higher hourly wage than Full-time and Part-time employees. This extra money paid to casual workers over and above the normal hourly rate that full-timers or part-timers get paid in the same job is known as Casual Loading. The employees may order stocks from different suppliers. This needs to be done at the end of every week to ensure that all VC stores are properly stocked. Your task as a database designer are as follows- 1. Write a report to Vintage Cellar business development manager and propose an entity relationship model and subsequent logical design. In your report, clearly justify your reason for selecting an entity relationship model and the various components relevant to your database design. 7. Create an SQL script for the follow scenarios provided below. a. Write a SQL query to print the details of all suppliers of Vintage Cellars. b. Write a SQL query to print the First Name, Last Name and current hourly payrate of all Vintage Cellar employees who are employed on a casual basis. c. Write a SQL query to print the Product Name and Cost of all products that belong to the Spirits sub-category. d. Write a SQL query to print the average cost of all products in each Alcoholic beverage product sub-category (average cost of all products in Wines, Spirits and Beers) e. Write a SQL query to show the total number of products belonging to each subcategory (Wines, Spirits, and Beers) within the Alcoholic beverage product category. Make sure to include screenshots for all your queries with before and after images for questions 4 to 7. 8. Discuss the future of database systems and how Vintage Cellar can benefit from future trends such as NoSQL, big data, data mining, data warehouse, and business intelligence. 9. Discuss the importance of implementing database security management, backup and recovery management. 10. Presentation You must prepare a presentation for the business development manager to discuss the EERD and database design. In your presentation, enlighten the business development manager on the future of database system and how Vintage Cellar can benefit from future trends such as NoSQL, big data, data mining, data warehouse, and business intelligence. Given the future trends, you also need to discuss the importance of implementing database security management, backup and recovery management. 2. Create an EERD using the Crow's Foot notation for the above business rules given. (Tip: You can use www.draw.io or www.lucidchart.com for your EERD). In the EERD, you have to identify all entities, relationship, and cardinalities and provide the reasonable attributes (where necessary) including primary keys and foreign keys. You will need to include an image of your EERD in the report. 3. Given your EERD, create a database and relevant tables from your EERD using the CREATE statement. Assign proper constraints and data type to each column. Make sure your tables are in 3NF. 4. Populate each table with at least 7 rows of data using the INSERT statement. 5. Use the ALTER TABLE statement to modify the structure of a table in the database. 6. Use the UPDATE statement to modify some records in a table. 7. Create an SQL script for the follow scenarios provided below. design a database management system for a fictional business. You will write a report which must propose an entity relationship diagram (ERD) model and subsequent logical design. The physical database implementation for the proposed model should be developed with MySQL. You will also present your design to the class. The report is worth 20%. You will also present your design to the class. The presentation is worth 10% Due date Word limit Other requirements Submission format 11.55pm Friday, Week 10 (11.55pm 11 Aug 2023) 2,500 Use 12 pt font Double-space your document to allow room for feedback State all your names and student numbers in the document header State the word count on the cover page of your report Include a bibliography at the end of your report APA referencing style Complete and submit a peer and self-evaluation form PDF file (.pdf) or Word (.doc) only for the project report PDF file (.pdf) or Word (.doc) only for the peer and self-evaluation form PDF file (.pdf) or PowerPoint (.ppxt) only for the presentation SQL statements in a text file or as SQL script (.sql) Business Problem Vintage Cellar (VC) is a small liquor retailer based in Darwin that sells both alcoholic and non- alcoholic beverages to its customers. The business has seen tremendous growth despite being in the infant stage as the business began trading only 7 months ago. VC recently approached your IT consulting firm, Darwin IT Consulting, and enquired about implementing a database solution. Vintage Cellar is keen to implement the database solution for three main functions, namely Inventory Management, Human Resource Management, and Supplier Relationship Management. After the initial meeting with VC, your team noted down several business rules for each of the three functions previously mentioned: VC stores information on all products that VC currently stocks and sells. The business stocks and sells both Alcoholic and Non-alcoholic beverages. Within the Alcoholic beverage category, there are 3 major sub-categories, namely Wine, Spirits and Beers. An employee may be employed on a Full-time, Part-time or Casual basis. Casual employees receive 25% higher hourly wage than Full-time and Part-time employees. This extra money paid to casual workers over and above the normal hourly rate that full-timers or part-timers get paid in the same job is known as Casual Loading. The employees may order stocks from different suppliers. This needs to be done at the end of every week to ensure that all VC stores are properly stocked. Your task as a database designer are as follows- 1. Write a report to Vintage Cellar business development manager and propose an entity relationship model and subsequent logical design. In your report, clearly justify your reason for selecting an entity relationship model and the various components relevant to your database design. 7. Create an SQL script for the follow scenarios provided below. a. Write a SQL query to print the details of all suppliers of Vintage Cellars. b. Write a SQL query to print the First Name, Last Name and current hourly payrate of all Vintage Cellar employees who are employed on a casual basis. c. Write a SQL query to print the Product Name and Cost of all products that belong to the Spirits sub-category. d. Write a SQL query to print the average cost of all products in each Alcoholic beverage product sub-category (average cost of all products in Wines, Spirits and Beers) e. Write a SQL query to show the total number of products belonging to each subcategory (Wines, Spirits, and Beers) within the Alcoholic beverage product category. Make sure to include screenshots for all your queries with before and after images for questions 4 to 7. 8. Discuss the future of database systems and how Vintage Cellar can benefit from future trends such as NoSQL, big data, data mining, data warehouse, and business intelligence. 9. Discuss the importance of implementing database security management, backup and recovery management. 10. Presentation You must prepare a presentation for the business development manager to discuss the EERD and database design. In your presentation, enlighten the business development manager on the future of database system and how Vintage Cellar can benefit from future trends such as NoSQL, big data, data mining, data warehouse, and business intelligence. Given the future trends, you also need to discuss the importance of implementing database security management, backup and recovery management. 2. Create an EERD using the Crow's Foot notation for the above business rules given. (Tip: You can use www.draw.io or www.lucidchart.com for your EERD). In the EERD, you have to identify all entities, relationship, and cardinalities and provide the reasonable attributes (where necessary) including primary keys and foreign keys. You will need to include an image of your EERD in the report. 3. Given your EERD, create a database and relevant tables from your EERD using the CREATE statement. Assign proper constraints and data type to each column. Make sure your tables are in 3NF. 4. Populate each table with at least 7 rows of data using the INSERT statement. 5. Use the ALTER TABLE statement to modify the structure of a table in the database. 6. Use the UPDATE statement to modify some records in a table. 7. Create an SQL script for the follow scenarios provided below.