Logical database design for the case study

Assignment Help Database Management System

Reference no: EM132498216

Objectives
• To perform physical database design
• To develop queries

Case Study: Car4U

Physical Database Design

In this assignment, first, you need revised the logical database design for the case study based on the feedback you received from Assignment 1.

Then, follow the physical database design process to translate logical data model for target DBMS into a MySQL database. You need to create tables, Queries and forms using MYSQL and LibreOffice Base. You also need to create SQL queries and views, normalised tables and create E-R Diagrams.

Part 1. Use MYSQL workbench to create database in MYSQL server. Create EER diagram in MYSQL Workbench. Use MSQL workbench to create ad EER diagram (tables, relationships, PK, FK, indexes) based on your logical database design (5 Marks)

Part 2. Create database tables and any appropriate database views, stored procedures, triggers. Then, enter hypothetical data records with into your tables (minimum 5 records in each table) (10 Marks)

Part 3. Create Form using LibreBase or MySQL Work Bench (5 marks)
3.1 Create a Form for Car4U Manager to enter the information about Car4U cars
3.2 Create a Form for Car4U Manager to enter information about the Staff
3.3 Create a Form for Staff to enter the car rental information

Part 4. Create the following Queries (MySQL Work Bench or LibreBase): (10 Marks)
Query 1: Give last name of all customers who are now renting a car from our company.
Query 2: Give make and colour of all cars currently rented out.
Query 3: For each completed rental, give the rental price and rental id.
Query 4: List last name of all managers.
Query 5: List last and first names of all customers.
Query 6: Give a query that answers the question "Is any of our employees also our customer"?
Query 7: List customers sorted by their rental history (measured in Days) from high to low.
Query 8: Find rental id of all completed rentals.
Query9: Find the value of the cheapest (completed) rental. Utilise query 3 as the inner query. 3
Query 10: Give makes of the cars that have never been rented.

What you need to submit:
1. A MYSQL Workbench file (e.g. Car4U.mwb) containing your logical database design in the form of EER diagram.

2. A MYSQL dump file containing your database schema and data records. You should export your database as one self-containing file in one transaction, include database schema (This link has the steps for database export) https://dev.mysql.com/doc/workbench/en/wb-admin-export-import.html)

3. A text file containing all the SQL query scripts.

4. The LIbreBase XXX.odb file or MySQL workbench file contain the forms and/or queries. You must include all the above 5 files in one compressed zip file, and submit the zip file via the assignment 2 submission link on LEO.

Attachment:- Perform physical database design.rar

Reference no: EM132498216

Questions Cloud

Compare the major types of employment interviews : Compare briefly the major types of employment interviews described in this chapter (attached). Which type would you prefer to conduct? Why?

What is the total estimated direct labor cost for July : What is the total estimated direct labor cost for July assuming the direct labor workforce is adjusted to match the hours required

What is the best program to use to assist : What is the best program to use to assist you with correct citations?

What equal amounts can Maria withdraw : Question - Maria Alvarez is investing $300,000 in a fund that earns 4% interest compounded annually. What equal amounts can Maria withdraw

Logical database design for the case study : Logical database design for the case study - Create database tables and any appropriate database views, stored procedures, triggers

Determine the dollar amount that Brunswick will report : Use this information to determine the dollar amount that Brunswick will report on its fiscal year 2018 Balance Sheet for Paid in Capital Treasury Stock

What gross profit for period was : A high tech company reported sales of $220,000, cost of goods sold of $150,000 and inventory of $60,000. Gross profit for period was?

Develop an international human resources management strategy : Organizations have to develop an international human resources management strategy when they expand globally. Which do you think is more critical.

How many pounds of raw materials should be purchased : If 96,250 pounds of raw materials are needed to meet production in August, how many pounds of raw materials should be purchased in July

User Account

All Pages