Answer:
In this modern day, databases are based heavily on relational models in which tables are the fundamental building blocks. We may ask the question as to where ER comes in database design, its importance and the need for ER modelling in modern databases. In an attempt to answer these questions, we get to understand that ER is a high-level description of the proposed database, useful for understanding the requirements gathered from a client, in our case The Reproduction Antiques Company. It is a conceptual level data model and provides the concepts of entities, relationships and attributes.
The Reproduction Antiques Company database context
Entity: an animate or inanimate thing of independent physical or conceptual existence and distinguishable. …show more content…
From this was created a data dictionary, these are loosely defined according to the context from which they are read;
Data Dictionary And ER Diagram
Entities
Attributes
Description
Relationships
Product
product number, description, type, unit price, quantity in stock, next delivery date, supplier details primary key: product number
This describes the products that Reproduction Antiques Company sell. Each product belongs to a single product type, but each product type contains many different products. The product number is unique.
Included in, Supplied by
Supplier
supplier number, name, address, telephone primary key: supplier number
One supplier may supply many different products, but each product is only supplied by a single supplier. The supplier number is unique.
Supply
Retailer retailer number, name, address, telephone, maximum credit allowed, staff who processed registration, date the retailer joined primary key: retailer number
This describes the retailer who makes orders of products from the Reproduction Antiques Company. The retailer is registered by a staff at the company, whose data is also stored in the …show more content…
You should develop a final relational model, normalised to 3NF relations, using “short hand” notation. You should clearly indicate primary keys (Bold Underlined) and foreign keys (Bold Italic). Referential integrity constraints should be noted using linking arrows.
You should explain why you developed the design that you did (e.g., especially normalisation decisions). (13 marks)
Answer:
1NF, 2NF and 3NF were performed on the redundant data that was collected during the conceptual design. This resulted in a final ER model that was in 3NF, such that some irrelevant data that appeared in entities where they were not needed were removed.
Product ( productNum, description, type, unitPrice, quantityInStock, nextDelivery, supplierNum )
No Normalisation
Supplier ( supplierNum, name, address, telephone, productNum)
Normalised to;
Supplier ( supplierNum, name, address, telephone)
Retailer ( retailerNum, name, address, telephone, maxCredit, orderNum, staffNum)
Normalised to;
Retailer ( retailerNum, name, address, telephone, maxCredit, orderNum)
Staff ( staffNum, name, address, position, salary, dateEmployed,
In this modern day, databases are based heavily on relational models in which tables are the fundamental building blocks. We may ask the question as to where ER comes in database design, its importance and the need for ER modelling in modern databases. In an attempt to answer these questions, we get to understand that ER is a high-level description of the proposed database, useful for understanding the requirements gathered from a client, in our case The Reproduction Antiques Company. It is a conceptual level data model and provides the concepts of entities, relationships and attributes.
The Reproduction Antiques Company database context
Entity: an animate or inanimate thing of independent physical or conceptual existence and distinguishable. …show more content…
From this was created a data dictionary, these are loosely defined according to the context from which they are read;
Data Dictionary And ER Diagram
Entities
Attributes
Description
Relationships
Product
product number, description, type, unit price, quantity in stock, next delivery date, supplier details primary key: product number
This describes the products that Reproduction Antiques Company sell. Each product belongs to a single product type, but each product type contains many different products. The product number is unique.
Included in, Supplied by
Supplier
supplier number, name, address, telephone primary key: supplier number
One supplier may supply many different products, but each product is only supplied by a single supplier. The supplier number is unique.
Supply
Retailer retailer number, name, address, telephone, maximum credit allowed, staff who processed registration, date the retailer joined primary key: retailer number
This describes the retailer who makes orders of products from the Reproduction Antiques Company. The retailer is registered by a staff at the company, whose data is also stored in the …show more content…
You should develop a final relational model, normalised to 3NF relations, using “short hand” notation. You should clearly indicate primary keys (Bold Underlined) and foreign keys (Bold Italic). Referential integrity constraints should be noted using linking arrows.
You should explain why you developed the design that you did (e.g., especially normalisation decisions). (13 marks)
Answer:
1NF, 2NF and 3NF were performed on the redundant data that was collected during the conceptual design. This resulted in a final ER model that was in 3NF, such that some irrelevant data that appeared in entities where they were not needed were removed.
Product ( productNum, description, type, unitPrice, quantityInStock, nextDelivery, supplierNum )
No Normalisation
Supplier ( supplierNum, name, address, telephone, productNum)
Normalised to;
Supplier ( supplierNum, name, address, telephone)
Retailer ( retailerNum, name, address, telephone, maxCredit, orderNum, staffNum)
Normalised to;
Retailer ( retailerNum, name, address, telephone, maxCredit, orderNum)
Staff ( staffNum, name, address, position, salary, dateEmployed,