1 / 21

The Relational Database Model & The Database Development Process

Fact of the Week: According to a G artner study in ‘06, Microsoft SQL server had the highest annual growth of any proprietary DBMS at 28% increasing market share from 15.6% to 17.4%. The Relational Database Model & The Database Development Process. Learning Objectives.

chaney
Download Presentation

The Relational Database Model & The Database Development 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. Fact of the Week: According to a Gartner study in ‘06, Microsoft SQL server had the highest annual growth of any proprietary DBMS at 28% increasing market share from 15.6% to 17.4% The Relational Database Model & The Database Development Process

  2. Learning Objectives • Describe the Relational Model • Define relational terms and understand the terminology in practice. • Understand these relational terms through practice • Explain the System Development Life Cycle (SDLC) • Explain the Database Life Cycle (DBLC)

  3. Fundamental Axioms of DBMSs • Users communicate with computer applications (websites, etc) • Computer applications communicate with DBMSs. • Users do not communicate with DBMSs directly. • As a result DBMSs although they can be used interactively, are not used interactively. • A DBMS is not a replacement for sound database design principles.

  4. The Relational Database Model • Many DBMS applications implement the Relational Model, but none of them enforce it. This permits rookie database designers to shoot themselves in the foot. And many have (including yours truly). • The Relational Model has: (Codd’s 3 rules) • 1)Data Independence • Clear separation between data and metadata • 2) Data Consistency • Minimal redundancy; the data adopts the “DRY” principle • 3) Easy to use • You don’t have to understand the implementation to use it.

  5. You can build a poorly-designed DB in a DBMS Do you see problems with this database design?Think Codd: Independence? Consistency? Ease-of-Use? Change Sally Jones’s phone #? How many ‘Software’ in the database? Add New Employee Bob Smith? Delete Dave Smith (no longer works here)

  6. Activity: Relational Terminology Identify Each of These : • Table • Relation • Row • Column • Tuple • Attribute • Physical Domain • Logical Domain

  7. DBMS : Physical Domain Different “flavors” of DBMSs use different data types.

  8. DBMS: Logical Domain • Default Value – a value entered into an attribute for a row when one isn’t specified. • Check Constraint – an expression which must be evaluated prior to the insertion of a row. Eg. Employee_hourly_wage >= 0 • Unique Constraint – ensures duplicate values are not inserted into a column. (Secondary Keys should have unique constraints) • Lookup table – a separate table containing all of the acceptable values for a given column, typically varchar (Think drop down list) The column you’re trying to constrain is a FK to the lookup table.

  9. Example: Lookup Table Foreign Key

  10. Activity: The Relational Table Relation Name? Attributes? Physical Domain of Columns? Logical Domain of Columns? Candidate Keys?

  11. Activity: Find the keys • Candidate? Primary? Secondary? Foreign? Surrogate?

  12. One more time. Tables Rule for joining tables Columns: Phys. Domain. Log Domain? Null? Keys: Candidate? Primary? Foreign, Surrogate, Secondary?

  13. Activity: Which of these is a good PK? Candidate keys? Best primary key? Why? Should a Surrogate key be used?

  14. The Natural Join at Work NaturalJoin

  15. Activity: Where’s the Integrity? Which of the 4 tables exhibit Entity Integrity? Which of the 3 relationshipsexhibit Referential Integrity?

  16. Example: Implementation of a 1-M Relationship

  17. Example: Implementation of a M-N Relationship This M-M Relationship has been resolved into two 1-M relationships

  18. Example:Null and Flags Nulls typically cause problems in Varchar andNumeric, and bit fields 103 and 105 are null for different reasons! Null makes sense for this column Flags used to represent different status

  19. Data Models: Abstraction Levels Conceptual Highly AbstractHardware and Software Independent Logical Somewhat Abstract Hardware Independent Software Dependent Internal External Not Abstract (Concrete)Hardware and Software Dependent Physical

  20. SDLC / DBLC IPlanning IIAnalysis IIIDesign IVImplementation VMaintenance &Support Resources LogicalModel Conceptual Model PhysicalModel Internal / ExternalModel Time Systems Development Lifecycle

  21. The Relational Database Model & The Database Development Process Questions?

More Related