Logical Modelling Exercises Part 2

1 / 10

# Logical Modelling Exercises Part 2 - PowerPoint PPT Presentation

Logical Modelling Exercises Part 2. Answers?. Explain the database anomalies in the following table:. Anomalies?. What is the Primary Key? Let us asssume it is Project_Code . Insert: Need to know details of Project &amp; Staff &amp; Pay in order to insert a ‘complete’ new record .

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about ' Logical Modelling Exercises Part 2' - naida-gibson

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Logical Modelling ExercisesPart 2

Anomalies?
• What is the Primary Key?
• Let us asssume it is Project_Code.
• Insert:
• Need to know details of Project & Staff & Pay in order to insert a ‘complete’ new record.

AND

• I will have to insert redundant data if a member of staff is allocated to multiple projects.
• Delete:
• If I delete the record for ‘White’ I will also loose the Pay_Scale details for Grade ‘B2’.
• Update:
• If the Pay_Scale for Grade ’A2’ changes to ‘5’, I have to remember to change multiple records.
What would a ‘better’ set of tables (relations?) to store the data look like?

Staff

Project

Pay

2 ABC manages a customer purchasing system for which you must develop a Database Schema (i.e. Logical Data Model), based upon the following ER Diagram.

• NB.
• There is NO Many-to-Many relationship between Customer & Order.
Schema: CustOrdProd
• Domains:
• BigNumbers: Integer, >0.
• MediumNumbers: Integer, >0, <1000
• SmallNumbers: Integer, >0, <=100.
• Names: Text, Title Case, <= 35 Characters.
• GeneralText: Text, <=25 Characters.
• PostCodes: Text, Upper Case, >= 7 & <= 8 Characters.
• TelephoneNumbers: Text, [0-9], >= 6 & <= 15 Characters.
• Prices: Currency, £, >10 & <=250, 2 Decimal Places.
• LongDates: Date, dd/mmm/yyyy.
Relation: Customer
• ID: BigNumbers;
• Name: Names;
• Street: GeneralText;
• Town: GeneralText;
• PostCode: PostCodes;
• TelNo: TelephoneNumbers;
• Primary Key: ID
Relation: Order
• ID: BigNumbers;
• CustID: BigNumbers;
• Date: LongDates;
• Primary Key: ID
• Foreign Key: CustID references Customer.ID
Relation: Product
• ID: BigNumbers;
• Name: Names;
• Description: GeneralText;
• CostPrice: Prices;
• SalePrice: Prices;
• QtyInStock: MediumNumbers;
• Primary Key: ID
Relation: ProdOrd
• OrderID: BigNumbers;
• ProdID: BigNumbers;
• QtyOrderd: SmallNumbers;
• PricePaid: Prices;
• Primary Key: OrderID & ProdID
• Foreign Key: OrderID references Order.ID
• Foreign Key: ProdID references Product.ID