Reference no: EM132667575

ITDA1001 Database Fundamentals - Victorian Institute of Technology

Learning Outcome 1: Explain the theoretical approaches used in database development and the issues related to data management in an enterprise

Learning Outcome 2: Explain the fundamentals of database languages, models and architecture LO3 Apply relational modelling concepts and principles to design a database Learning Outcome 3: Use normalization levels and implement these for data storage

Learning Outcome 5: Apply database knowledge and techniques to design and implement a database management system

Learning Outcome 6: Critically analyze the data access issues as well as techniques for database administration, integrity and security in the implementation and use of a database

Overview:

In this assignment, you are required to answer the short questions, create the ER Diagram, normalize tables, develop SQL statements to demonstrate your ability to use Select, Update, Delete, Create, Alter, Drop statements and show your ability to create Views and Procedures.

Questions:

Q1 Define the following terms that are related to relational model. Provide an example in your answers? (5 marks)
a. Candidate Key
b. Primary Key
c. Foreign Key
d. Entity Integrity
e. Referential Integrity

Q2 Draw a relational model for the following case study (9 marks) Case Study
The local under-sixteen football league needs a database to help track teams, children that sign up to play in the league, the parents of these children and the coaches for each team.
The league wishes to record the details for each parent of a particular player (the parents last name, first name, phone contact number and address). For each player, the system needs to record the player's last name, first name, blood type and their date of birth. Any allergies that the player has also need to be recorded.

A player may only play in one team with this league. Each team is given a unique id, the system needs to record this id, the teams name, and the city their home ground is located (a city may have several under-sixteen teams based in it). A team's colours are also to be recorded, teams may choose to use a single or multiple team colours. A team may have several coaches - one of the coaches is designated as the head coach.

A coach is only permitted to coach one team in the under-sixteen leagues. All communications from the league to the team are via the head coach. To be registered to play in this league a team must have a head coach and at least one player. The database needs to track a coach's first and last name, phone and address contact details and the team that they are coaching.

Q3 Convert the following table into Third Normal Form (3NF). Clearly mention each step of the process. You can make the appropriate assumptions and key(s) for your normalized tables.

Q4 Write SQL statements for following:
• Retrieve a list of Northwind's Customers (names) who are in cities where there
are no suppliers.
• List all the product names from Tokyo Traders where the product's unit price is greater than the average unit price of all products.
• List all those cities that have both Northwind's Supplier and Customers.