Prepare the following: a) An ER diagram for the system. Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities. You must use the Finkelstein...

Prepare the following:


a)    An ER diagram for the system. Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities. You must use the Finkelstein  methodology as per the study book and tutorials.         (15 Marks)


b)    A list of relations (equivalent to Finkelstein entity list). Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned. You must use the Finkelstein methodology as per the study book and tutorials.         (15 Marks)


c)    A single SQL statement or multiple statements that create/s the table for the relation training courses for lawyers and interns. All key and attribute constraints should be included and data types suitable to each attribute should be chosen.

(10 Marks)




SECTION B    (Normalisation) (30 marks)


Produce a set of relations (equivalent to the Finkelstein entity list) in third normal form (3NF) from the following un-normalised relation. Show your working and entitles for 1NF, 2NF and 3NF.   You must use the Finkelstein methodology as used in the study book and tutorials.


INSURANCE PORTFOLIO (portfolio id, insurance company name, insurance company phone, ((agent name, agent license number, state of residence, ((policy number, policy description, annual premium, benefit, beneficiary details)),

number of policies)), number of policies in a portfolio)


Notes:
1.    A portfolio id uniquely identifies an insurance portfolio.
2.    One insurance portfolio is from a single insurance company only.
3.    A portfolio comprises dealing with multiple agents and each agent can sell multiple policies in the same portfolio.




SECTION C (SQL) (30 marks)
MARKING CRITERIA



1. Five marks awarded for each correct SQL statement.
2. Alternative approaches to the model answer could be accepted unless they do not follow the rules / requirements set out in the specification, are poorly optimised or are poorly constructed (SQL).

3. Part marks may be awarded if an answer only has a small problem or an alternative solution is presented that works but is not ideally optimised / constructed.
4. There are 6 questions for the total of 30 marks.


Below is a reproduction of the ERD for the JustLee books database.  It should help you navigate the tables in the database.  The database script to create the tables is located on the Moodle site under the assignment specifications.  If you have run a version of the script earlier in the semester please run it again to ensure that you are using the correct version of the tables.  The ERD diagram may not necessarily have all the fields listed so you might have to refer to the description of the table by using the DESCRIBE command in Oracle.