Database fundamentals UE Past Papers Questions.

Notown Records has decided to store information about musicians who perform on its albums (as well as other company data) in a database. The company has wisely chosen to hire you as a database designer (at your usual consulting fee of $2,500/day).

1.  Each musician that records at Notown has an SSN, a name, an address, and a phone number. Poorly paid musicians often share the same address, and no address has more than one phone.
2.  Each instrument that is used in songs recorded at Notown has a name (e.g., guitar, synthesizer, flute) and a musical key (e.g., C, B-flat, E-flat).
3.  Each album that is recorded on the Notown label has a title, a copyright date, a format (e.g., CD or MC), and an album identifier.
4. Each song recorded at Notown has a title and an author.
5. Each musician may play several instruments, and a given instrument may be played by several musicians.
6.  Each album has a number of songs on it, but no song may appear on more than one album.
7.  Each song is performed by one or more musicians, and a musician may perform a number of songs.
8.  Each album has exactly one musician who acts as its producer. A musician may produce several albums, of course.

Respond to the following questions
i. Identify the entities and attributes
ii. Identify the key for each entity
iii. Identify the relationships and types of relationship
iv. Draw the ERD for Norton Records
v. State any assumption used

Answer / Solution

Consider the following Database schema

Professor (SSN, FirstName, middleName, LastName, salary, rank)

Project (Pid, SSN, Title, startyear,location,amount, sponsoredBy)

Student (regNo, SSN, FirstName, middleName, LastName, yearOfStudy, PID)

USING SQL COMMANDS PERFORM THE FOLLOWING TASKS

1. Create a database with a name UniversityProjects_db
2. Create the professor, project and student tables. Assign primary key for each table

Answer / Solution

Consider the following scenario
A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name and age as attributes). Employees work in departments; each department is managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company.
Respond to the following questions
i. Identify the entities
ii. Identify the attributes for each entity
iii. Identify the relationships and types of relationship
iv. Draw an ER diagram that captures this information.
v. State any assumptions used during your design

Answer / Solution

A company database need to store information about employees (identified by ssn, with first name, middle name, surname, salary and phone), departments(identified by dno, with dname, address and budget) and children of employees (with name and relationship and DOB). Employees work in departments, each department is managed by an employee, assume that only one parent works for the company) is known. We are nt interested in information about a child once the parent leaves the company.

1. Identify the entities and types of each entity
2. Identify the attributes and primary keys for each entity
3. Identify the relationship and type of relationship
4. Draw the ER diagram that captures this information
5. Translate the ERD drawn in 4 above to relational schema.

Answer / Solution

What is the long form of SQL?

Answer / Solution