This presentation is the property of its rightful owner.
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 & Staff & Pay in order to insert a ‘complete’ new record .

Logical Modelling Exercises Part 2

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 - - - - - - - - - - - - - - - - - - - - - - - - - -

## 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