logical modelling exercises part 2
Download
Skip this Video
Download Presentation
Logical Modelling Exercises Part 2

Loading in 2 Seconds...

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

slide5

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
ad