Practical 3 - Suppose we have already designed 2 tables, Album and song, to keep track of PDF

Preview

Summary

Download Practical 3 - Suppose we have already designed 2 tables, Album and song, to keep track of PDF

Description

PRACTICAL 3 Part A: Week 3 Question#1: Suppose we have already designed 2 tables, Album and song, to keep track of our favourite songs along with their relevant album if any. For each song, we need to record the songTitle, the name/s of the singers, the attribute yearPublished as the year the song was published, and the album it belongs to if any. For each albumTitle and name of the producer. It is already explained in the lecture that a reasonable design for these information needs leads to the table structure on the right. Consider the construct of these tables, which field essentially plays the key in the other table, and how is it normally referred to? Album Answer: albumID {PK} albumID which is a primary key in Album table essentially plays as the albumTitle foreign key in Song table. A primary key songID in song table and producer albumID in Album table can exist own its on but a foreign key albumID in Song table can not exist without having the Song primary key in the referenced table of Album. A single album can songID {PK} contain more than one songs that is why they use albumID as songTitle foreign key in Song table. yearPublished 0..* singer albumID {FK}

Question#2: In this particular example of Song and Album, what type of relationship exists? Explain.

Answer: One to Many relationship exists in the above given example because one album can contain more than one Song. And We can also see from above table that on Album side it is (0..1) and on Song table side it is (0..*) which represents one to many relationship between Album and Song tables.

Question#3: If you are to produce the following tables, which table needs to be filled first and why?

Answer: Album table needs to be filled first because Song table contained the reference id (foreign key) of albumID. And as we know that the foreign keys cannot exist without having a primary key in referenced table(Album) for that purpose Album table must be created first.

0..1

Question#1: What is the difference between a relational diagram and a GRD?

Answer: ERD/GRD In ERD/GRD we are visualising the entities, their attributes, and the relation between them at conceptual level. we donot care about where foreign keys go or how to implement the relationships. However, we identified the primary keys at this level also. Symbolic representation. Conceptual level understanding for us.

Relational diagram In Relational diagram we are referring to an implementation of our model. Through this diagram we clearly define the formats of our attributes, the foreign keys and the relations in such a way that the database can understand. Tabular format. Implementation level that database can understands.

Modelling Activity Question#1: Given the following descriptions, create an appropriate ER diagram for each of the specified relationships. a) Each company operates four departments, and each department belongs to one company.

operates

Company 1..1

Department 4

b) Each department in part(a) employs one or more employees, and each employee is employed by one department.

Employee

employs

Department 1..1

1..*

c) Each of the employees in part(b) may or may not have one or one more dependents, and each dependent belongs to one employee.

Dependent

has

Employee 1..1

0..*

d) Each employee in part (c) may or may not have an employment history. Employee

Employment History

provides 1..1

0..*

Question#2: Represent all of the relationships described in Question1 as a single ER diagram.

operates

Company

Department

1..1

4

Employee

Dependent

1..1

0..* 1..1 employs

1..*

Employment History

Employee

provides 0..*

1..1 1..1 has

0..*

Dependent

Question#3: Add at least 3 attributes in each entity.

Answer:

Part 2: Create Database create database practical3

Create Tables create table Album( albumID int primary key, albumTitle varchar(25) not null, producer varchar(35) ); create table Song( songID int primary key, songTitle varchar(45), yearPublished int, singer varchar(40), albumID int, check(yearPublished >= 1800 AND yearPublished...