Data and Information Retrieval (Includes SQL coding);

Data and Information Retrieval (Includes SQL coding); Task 1: Database Design (This task is worth 2 0 marks) Introduction Condor Building Services Ltd is a long established construction supplier for construction companies based in Coventry and the surrounding area. The company specialise in supplying construction equipment such as piping, timber, wiring, plumbing UPVC frames and doors and masonry. As construction demand is slowly picking up Condor has regular orders for equipment to meet the needs of its client base. In order to support this, an orders book, see table below , is maintained in which equipment orders for its clie nts are recorded. For each order the client’s details, date, equipment, quantity, unit price and overall price of the order is logged. Order ID. Supplie r ID. Client Name Client Address Date Equipment Qty Unit Price Total Price CON - 2237 16 8 Coventry Building Services Ltd Units 2 - 4, Binley Industrial Estate, CV3 2WL 14/12/2014 Butterfly valve 2 £5.00 £99.00 ¾” Locknut 6 £1.50 Sch 40 Blk Pipe 4 £20.00 CON - 3664 527 Allied Construction Ltd 34, Lythalls La Industrial Estate , CV6 6RG 16/01/2015 Thin Stranded Copper Wire 6 £6.00 £36.00 CON - 2356 169 Ricoh Builds Ltd Unit 12, Stoneleigh Park, CV8 2UV 12/02/2015 Sch 40 Blk Pipe 3 £20.00 £280.00 4x8x3/4 Cos Plywood 2 £10.00 ¾” EMT 2 £50.00 Duplex Ivy Rec 1 £100.00 CON - 1234 032 Grand Designs Ltd 32 - 34, Bilton Industrial Estate , CV3 5YB 16/04/2015 Sch 40 Blk Pipe 1 £20.00 £23.00 ¾” Locknut 2 £1.50 Currently there is no database being used for managing the orders in the table above. It is therefore necessary to convert the orders table into a set of database relations by applying the process of normalisation to determine the correct relations. This task is split up into four activities that will ask you to normalise the current data inventory to third normal form to produce appropriate relations (tables) for the database. You will then be asked to create an ER diagram of the normalised relations . Activity 1: Put data in First Normal Form : Remove Repeating Elements or Groups of Elements in Data Activity 2: Put data Second Normal Form: Remove Partial Dependencies on a Concatenated Key in Data Activity 3 : Put data in Third Normal Form: Remove Dependencies on Non - Key Attributes / Final Database Design Activity 4 : Create an ER diagram of the normalised relations derived from tasks 1 to 3. Show the attributes for each entity, identifier attribute(s) and the correct relationships that exist between the entities taking care to show the correct relationship cardinalities. Evi dence Submit your answers to the activities (1 to 4) in a report section. The report section should contain diagrams and brief written explanations providing evidence how the normalisation process for each normal form (1 st , 2 nd and 3 rd ) was applied to the inventory table. For activity 4 you also need to produce an ER diagram showing your normalised entities. Marking Criteria – Task 1 Achieve 8 Marks (40%) Achieve Over 14 Marks (70%) • Evidence of partially correct normalisation applied to the identified database relations for achieving each normal form. The relations produced for each normal form should show their attributes and key fields and be populated with some data to illustrate the normal form the database is in. • For each activity a brief explanation of design decisions should be provided. • A Simple ER diagram showing the normalised entities identified their attributes, and identifiers. The diagram should show appropriate relationships between the entities with partially c orrect cardinalities. • A simple relational model showing the relations (tables) corresponding to the identified entities, their attributes and key fields clearly indicated. • Evidence of normalisation correctly applied to the identified database relations for achieve each normal form. The relations produced for each normal form should show their attributes and key fields and be populated with data to correctly illustrate the normal form the database is in. • For each normalisation step an explanation and jus tification of how and why it was applied. • A detailed ER diagram showing all the entities correctly identified from the problem description, their attributes, and identifiers. The diagram should show the correct relationships between all the entities with the correct cardinalities. • A detailed relational model showing the relations (tables) corresponding to the identified entities, their attributes, key fields and any foreign key fields clearly indicated. PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT :)