Mr ENDELLY / tatsopt@gmail.
com
Tutorial & Practicals on Database design, Normalization and Implementation
Part1: Design
Consider the following database requirements for a music recording company
Analyze the requirements given above and carry out the following activities:
1. Identify and describe all the entities in the following format
2. Draw the entity relationship diagram showing the various degrees of
relationship that exist between entities
3. Which of the entities can be considered as associative entities? Justify
Mr ENDELLY / tatsopt@[Link] 1
Mr ENDELLY / tatsopt@[Link]
Part2: Normalization
Consider the database requirements for a music recording company given above
4. Can the table above be considered as a flat file database? Justify
5. Which of the column headings would be suitable as a primary key?
6. Use the data from the data from the table to explain the various anomalies
present
7. Why is the table not in first normal form?
8. Normalize to the 1NF
9. Identify and discuss all functional requirements
10. Normalize up to the 3NF
11. Identify a composite key and justify it presence in that relation
12. Derive the ER diagram from the result obtained in 10
Part3: Implementation
Mr ENDELLY / tatsopt@[Link] 2
Mr ENDELLY / tatsopt@[Link]
Other tables are :
Mr ENDELLY / tatsopt@[Link] 3
Mr ENDELLY / tatsopt@[Link]
I. Using a DBMS of your choice, create a database called CDRecording
II. Use SQL commands to carry out the following operations:
1. Identify with justifications primary keys from each relation
alongside eventual foreign keys
2. Implement/create the four tables above with necessary constraints
3. Populate/insert the data into all the tables.
III. We are now concerned with the extraction of the following data. Write
only the SQL Command that does each of the following:
1. Display all the content of the CD table
2. List all RecordCompany (make sure you eliminate duplicated value from
the result set)
3. Display all CDTitle that were published in 2015
4. Display all MusicType and SongTitle (the first should be in ascending,
while the second is in descending order)
5. Display each MusicType with the number of SongTitle that corresponds to
it.
6. List all possible MusicType without any duplicated value
Mr ENDELLY / tatsopt@[Link] 4
Mr ENDELLY / tatsopt@[Link]
7. Which SongTitle matches with the SongID S1234?
8. Which MusicType matches with the ArtistID A318?
9. Display all ArtistName alongside their MusicType, avoid duplicated record
[Link] MusicType does the Artist JJ sing?
[Link] is the ArtistID of the Artist who sang the Title Waterfall?
[Link] is the Name of the Artist who sang the Title Waterfall?
[Link] all RecordCompany, the CDTitle they recorder alongside the
SongTitle and Which MusicType they correspond to
[Link] all RecordCompany, the CDTitle they recorder alongside the
SongTitle and Which MusicType they correspond to, for the only Artist
Maria Okello
[Link] the name of the Artist JJ to Jean Jacques
[Link] a new song of your choice
[Link] a Hip hop song sang by Suzzy
[Link] the RecordCompany GHK from the database
[Link] want to store the address of each Artist. Considering the actual state of
the database, Write the SQL command that does the job (Each Artist should
have a default address Buea subsequently)
IV. Answer the following questions:
1. What happens when you insert a new artist without his ID?
2. What happens when you insert a new song corresponding to the artistID B250?
Mr ENDELLY / tatsopt@[Link] 5
