Logical modelling exercises part 2
Download
1 / 10

Logical Modelling Exercises Part 2 - PowerPoint PPT Presentation


  • 99 Views
  • Uploaded on

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 .

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

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


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


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


2 store the data look like?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: store the data look like? 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: store the data look like? Customer

  • ID: BigNumbers;

  • Name: Names;

  • Street: GeneralText;

  • Town: GeneralText;

  • PostCode: PostCodes;

  • TelNo: TelephoneNumbers;

  • Primary Key: ID


Relation order
Relation: store the data look like? Order

  • ID: BigNumbers;

  • CustID: BigNumbers;

  • Date: LongDates;

  • Primary Key: ID

  • Foreign Key: CustID references Customer.ID


Relation product
Relation: store the data look like? Product

  • ID: BigNumbers;

  • Name: Names;

  • Description: GeneralText;

  • CostPrice: Prices;

  • SalePrice: Prices;

  • QtyInStock: MediumNumbers;

  • Primary Key: ID


Relation prodord
Relation: store the data look like? 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


ad