BN204-Database Technologies
Question 1 Data Modelling
Create a Crow’s Foot Notation Entity Relationship Diagram (ERD) to support the following business operations:
An online food ordering system wants to keep track of their customers, orders, food items, and employee details in order to streamline their administration and operational tasks. The company has wisely chosen to hire you as a database designer. You are required to design the data model for the new database called “FoodOnline” that satisfies the constraints and user view requirements given below
User view 1 requirement: Restaurant has employees, each identified by a unique employee number, first name, last name, address, and phone.
User view 2 requirements: Customer visits the restaurant, online and place orders. First, customer should register by entering their credentials (email, name, phone, address, and password). Then, they order by specifying the food codes in the menu and quantity of the food required.
User view 3 requirements: For each order,
order_ID,
customer_ID,
Total_price, Total_items, and date will be recoded.
The restaurant maintains a staff roster to ensure that there are sufficient staff in the shift. allocates one employee per order. Those employees who are working in therestaurant are assigned to zero or more orders.
User view 4 requirements: Each order contains specified quantities of one or more food items. Every food item may appear zero or more times in an order table.
User view 5 requirements: For each food item presented in the online menus is identified by a unique food_code, a food name, and price.
User view 6 requirements: Customer payment details will be recoded in Payment table with the following information: Payment_ID, order_ID, customer_ID, and Total_payment.
User view 7 requirements: Once the payment is completed and the order is ready to deliver, delivery will be organised. Registered drivers may or may not have a delivery assigned, and they may have more than one delivery order.
a) Identify 4 business rules describing the relationships between entities and write the type of relationship, as given below, based on the description of operations given in the question.
b) Identify entities, attributes, and primary keys that adequately describe the entities. Construct them as a collection of Tables (e.g. given below). You are required to indicate primary keys (underline).
c) Determine the functional dependencies (FD). Normalise these relations to 3rd normal form, ensuring that the resulting relations are dependency-preserving. You need to explain each step clearly
d) ER diagram
a. Represent the structure of your database visually by using an entity-relationship (ER) diagram. You are required to:
i. Draw the ER diagram on paper (do not use any software tools) and include the image in your Word document.
ii. Name all entities in the ER diagram including your name. For example: If you have Entity name Student, you will name it as “Yourname:Student”. If “Sam Smith” is drawing the ER diagram he will name the “Student” entity as “Sam_Smith:Student”.
iii. If you make any assumptions about data that are not explicitly given in the problem, these must be described
Question 2
Demonstrate skills in building a database
Consider the following relational schema. These relations keep track of formation about musicians who perform on its albums in a database. Each instrument that is used has a name (e.g. guitar, flute, ...) and a unique musical key. Primary keys have been underlined.
MUSICIAN (M_ID, M_Name, phone_number, Address, state)
INSTRUMENT (Musical_key, Instru_Name, price)
MUSICIAN_INSTRUMENT (M_ID, Musical_key)
ALBUM (Album_ID, Title, Copyright_date, M_ID)
An instance of this relational database schema is given below.
a) First, you need to create the database in MS Access. Enter another 3 records (excluding the examples given above) to the database. You need to use your MIT ID in the Musician table.
You are required to include following screen shots here.
1. Relationship diagram created in Access. (Select database tools -> Relationships in the Access menu)
2. Data sheet view and design view of your tables.
b) By using the database developed, write SQL queries to answer the following. Include screen shots of the outputs, the query (do not attach screenshot of the query)
1. Write SQL code required to list details of all the Musicians from the state of Victoria, organize the list in descending order of Musician’s name.
2. Find the highest price in the instrument table.
3. Find the M_ID of Musicians who paly guitars.
4. Modify the above query to find the total number of musicians registered in the system who play guitars.
5. List the M_ID and the name of the musician and the titles and Copyright_dates of their albums.
6. For each musician, list M_ID and the number of Albums recorded in the system
Question 3
Integrity and security of the database
a) Data integrity issues are the most prevalent in databases. What are the different types of integrity rules that we have to consider when designing a database to minimise data integrity issues?
b) What are the keywords available in MS Access to use in SQL queries to enforce these data integrity constraints? Discuss these with proper SQL queries.
