Question 1. Normalisation (6 points).

Consider the following relational database schema about employees from departments working on projects:

Employee(empNo, givename, surname, DOB, gender, deptNo, dept_name)
Department(deptNo, dept_name, location, manager)
WorkOn(empNo, projNo, proj_name, deptNo, hours)

Some notes on the semantics of attributes are as follows:
⦁ Each employee has a unique employee number (empNo) and work for a department.
⦁ Each department has a unique department number (deptNo), a manager and possibly several locations.
⦁ Each project has a unique project number (projNo). A project also has an “owning” department.
⦁ Employees work on projects for some hours.

FDs based on business rules are given as follows:

⦁ empNo givename, surname, DOB, deptNo, dept_name
⦁ deptNo dept_name, manager
⦁ projNo proj_name, deptNo, dept_name
⦁ empNo, projNo proj_name, deptNo, hours

Answer questions below:
⦁ (3 points) Identify and annotate the primary key (underline) and any foreign keys (asterisk) for each given relation. Give the highest normal form for each relation. Explain your answer using the given FDs. Note that relations must be written in the form as shown in the examples below:

Student(sno, name, address)
Course(cno, title)
Take(sno*, cno*, grade)

1.2 (3 points) Decompose any given relation not in BCNF or 3NF into relations in BCNF or 3NF. Your decomposition must keep all functional dependencies and must be lossless. Show your working for the decomposition. For each resultant relation, explain if it is in BCNF or 3NF and annotate the primary key (underline) and any foreign keys (asterisk).



Question 2. The ER Model (8 points).
The XB Records management has decided to build a database storing information about artists who perform for their albums. The company has hired you to design the database. Description of the database is as follows.

⦁ Each artist has a unique ID, a name, and an address.
⦁ A band has a name, year established and place of origin. A band has several artists. An artist usually is a member of only one band at a time.
⦁ Each song has an ID and a title.
⦁ Genres of songs are described by a unique name, some description. For example, “rock” is a genre with the description “rock music originated as ‘rock and roll’ in the United States”. A genre may have many songs.
⦁ Each album recorded on the XB label has a title, a release date, a format (such as CD, MP3), and a unique album number. Each album has many tracks on it (up to 20 usually), and they are numbered sequentially. Each track of an album usually corresponds to a song.
⦁ An album may be by an artist or a band (but not both).
⦁ A song may appear on several albums. A song has one or more genres, and a genre may have many songs.

Examples for queries on the database:
⦁ Look up the details of artists, bands, songs and albums.
⦁ List the songs and their track number for the album “Thriller” (title).
⦁ Give the number of songs for each genre.
⦁ Find the albums by an artist named “Bob Janes”.
⦁ Give the name of the band for the album “The Last Waltz”.
⦁ How many albums have the song “Almost is Never Enough”?

According to the data requirements and query requirements above, give the ER diagram for the database using the UML class diagram symbols (as used in the lecture notes and tutorials), making appropriate assumptions where necessary. You must represent entity types, relationships and their attributes, and all applicable constraints in your diagram. Explain any constraints that are not expressed in the diagram. Note that your ER diagram would be mapped to a relational database schema and implemented as tables using a DBMS. It may be helpful that you do this mapping and see if the queries can be answered using the mapped relations to help you refine the ER diagram.

Question 3. ER to Relational Schema Mapping (6 points).
Consider the ER diagram for a Software Testing database below using UML class diagram symbols. Map the diagram to a relational database schema. Indicate the primary key (underline) and any foreign keys (asterisk) for each relation. Note that a relational database schema comprises a set of relation schemas written in the form as shown in the examples below:

Student(sno, name, address)
Course(cno, title)
Take(sno*, cno*, grade)

Get 20% Discount on This Paper
Pages (550 words)
Approximate price: -

Try it now!

Get 20% Discount on This Paper

We'll send you the first draft for approval by at
Total price:

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Quality Custom Writers has assembled a team of highly skilled writers with diverse experience in the online writing circles. Our aim is to become a one stop shop for all your Academic/ online writing. Check out below our amazing service!


Essay Writing Services

At Quality Custom Writers, we prioritize on all aspects that creates a good grade such as impeccable grammar, proper structure, zero-plagiarism, and conformance to guidelines. The principal purpose of essay writing is to present the author's evaluation concerning a singular subject about which they have made. Since Professionalism is the mother of every success, try our team of experienced writers in helping you complete your essays and other assignments.


Admission Papers

You have been trying to join that prestigious institution you long yearned for, but the hurdle of an admission essay has become a stumbling block. We have your back, with our proven team that has gained invaluable experience over time, your chance of joining that institution is now! Just let us work on that essay.How do you write an admission essay? How do you begin the essay? For answers, try Quality Custom Writers Now!


Editing and Proofreading

Regardless of whether you're pleased with your composing abilities, it's never an impractical notion to have a second eye go through your work. The best editing services leaves no mistake untouched. We recognize the stuff needed to polish up a writing; as a component of our editing and proofreading, we'll change and refine your write up to guarantee it's amazing, and blunder free. Our group of expert editors will examine your work, giving an impeccable touch of English while ensuring your punctuation and sentence structures are top-notch.


Technical papers

We pride ourselves in having a team of clinical writers. The stringent and rigorous vetting process ensures that only the best persons for job. We hire qualified PhD and MA writers only. We equally offer our team of writers bonuses and incentives to motivate their working spirit in terms of delivering original, unique, and informative content. They are our resources drawn from diverse fields. Therefore your technical paper is in the right hands. Every paper is assessed and only the writers with the technical know-how in that field get to work on it.


College Essay Writing

If all along you have been looking for a trustworthy college essay service provider that provides superb academic papers at reasonable prices, then be glad that you search has ended with us. We are your best choice! Get high-quality college essay writing from our magnificent team of knowledgeable and dedicated writers right now!


Quality Assignment/Homework Help

We give the students premium quality assignments, without alarming them with plagiarism and referencing issues. We ensure that the assignments stick to the rules given by the tutors. We are specific about the deadlines you give us. We assure you that you will get your papers well in advance, knowing that you will review and return it if there are any changes, which should be incorporated.

× How can I help you?