1 / 20

The Design Process

Define Objectives Analyze the Current Database Define Table Structures Establish Table Relationships. Define Business Rules Determine and Establish Views Review Data Integrity. The Design Process. 1. Define Objectives. Mission Statement

kylar
Download Presentation

The Design Process

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Define Objectives Analyze the Current Database Define Table Structures Establish Table Relationships Define Business Rules Determine and Establish Views Review Data Integrity The Design Process

  2. 1. Define Objectives • Mission Statement • A succinct statement in general terms describing the purpose of the database • New Starz Talent Agency • Mission Objectives • A series of statements describing general tasks that the user can perform with the database. • They support the mission statement and help the designer to determine the various aspects of the database • New Starz Talent Agency

  3. Mission StatementNew Starz Talent Agency • The purpose of the New Starz Talent Agency database is to maintain the data in order to supply information that supports the engagement services we provide to our clients and the management services we provide to our entertainers. Back

  4. Mission ObjectivesNew Starz Talent Agency • Maintain information for our clients that ask for entertainment engagements. • Maintain complete entertainer information. • Keep track of all the engagements we book. Back

  5. 2. Analyze the Current Database • Interview management and users • Find out what kind of data is being used • Find out how they are collected and handled on a daily basis • Compile list of fields and calculated fields • This list serves as a starting point for building tables, then the database

  6. 3. Define Tables Structures • Determine entities • Identify their attributes • Define tables and fields • Establish keys • Define field specifications • Make sure that a table represents one entity (subject) with no duplicate fields

  7. 4. Establish Table Relationships • Determine which tables are directly related • Determine type of relationship--one-to many, one-to-one, many-to-many • In the case of many-to-many, use a link table

  8. 5. Define Business Rules • E.g.: • Use 2-letter abbreviation for state name • shipDate must be later than orderDate • daytimePhone number is required • An agent can handle a maximum of 20 clients • An employee salary cannot be more than $100,000

  9. 6. Determine Views • Some views users need to see: • Specific information from a single table • Specific information obtained from several tables • Summaries from several tables • Create Queries • What are the questions that the user wants answered?

  10. Queries • Which books are $25 or less? • What is the phone number of publisher Big House? • What is the phone number of Melville? • Who publishes the book Macbeth? • All books published by Big House over $20.00 • All books written by Shakespeare and their publisher

  11. 7. Review Data Integrity • At table level, make sure each record is unique. • At the field level, check all field are specifications • At relationship level, check that relationships are valid, that there are no many-to-many, etc.

  12. Keys • Candidate Keys • Primary Keys • Foreign Keys

  13. Employees

  14. Candidate Keys • Field or set of fields that uniquely indentifies each record in the table • Must contain unique values • Cannot be null • Primary key will be chosen from these

  15. “Artificial” Candidate Key • Created if none of the fields qualify as candidate key • Or, if other candidate keys are complicated • Or, for efficiency (e.g., 5-digit number takes less time to compare than a 5-digit text)

  16. Primary Key • Chosen from Candidate Keys • Choose the “simplest” key.

  17. Elements of a Primary Key • Uniquely identifies each record in table • Contains unique values • Cannot be null • Contains minimum number of fields necessary to define uniqueness • Not optional • Its value should not be modified, except in rare cases

  18. Foreign Key • A Primary Key from one table becomes a Foreign key another table when that field is used to established a relationship between two tables. • While a Primary Key in a table has unique values, Foreign Key values are repeated.

  19. Foreign Key BuildingsbldNumber PK bldFloors bldElevator bldParking RoomsrmNumber PK bldNumber FK rmType rmSize rmPone 1 N

  20. Elements of a Foreign Key • Has the same name as the Primary Key from which it is copied (usually) • Has the same type as the Primary Key from which it is copied

More Related