Logical modelling exercises part 2
This presentation is the property of its rightful owner.
Sponsored Links
1 / 10

Logical Modelling Exercises Part 2 PowerPoint PPT Presentation


  • 73 Views
  • Uploaded on
  • Presentation posted in: General

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 .

Download Presentation

Logical Modelling Exercises Part 2

An Image/Link below is provided (as is) to download presentation

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 exercises part 2

Logical Modelling ExercisesPart 2

Answers?


Explain the database anomalies in the following table

Explain the database anomalies in thefollowing table:


Anomalies

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

What would a ‘better’ set of tables (relations?) to store the data look like?

Staff

Project

Pay


Logical modelling exercises part 2

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

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

Relation: Customer

  • ID: BigNumbers;

  • Name: Names;

  • Street: GeneralText;

  • Town: GeneralText;

  • PostCode: PostCodes;

  • TelNo: TelephoneNumbers;

  • Primary Key: ID


Relation order

Relation: Order

  • ID: BigNumbers;

  • CustID: BigNumbers;

  • Date: LongDates;

  • Primary Key: ID

  • Foreign Key: CustID references Customer.ID


Relation product

Relation: Product

  • ID: BigNumbers;

  • Name: Names;

  • Description: GeneralText;

  • CostPrice: Prices;

  • SalePrice: Prices;

  • QtyInStock: MediumNumbers;

  • Primary Key: ID


Relation prodord

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


  • Login