Relational Database Model & Database Development Process

1. Relational Database Model & Database Development Process IST359 M005 Yang Wang ywang@syr.edu 342 Hinds http://blackboard.syr.edu

2. Acknowledgements and caveat • These slides draw liberally, with permission, from the following sources: • IST359 materials by Prof. Michael Fudge Jr. • Caveat (beware): At best, PowerPoint slides are only a pale limitation of the entirety of a class meeting. In IST359 in particular, the lectures will cover topics beyond what appears in these slides. Don’t rely on them as a substitute for attending class.

3. 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 • Explain the Database Life Cycle

4. 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 • Explain the Database Life Cycle

5. The Relational Database Model • Many DBMS products 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 - abstracted • You don’t have to understand the implementation to use it.

6. You too can make a crappy DB like this one! Do you see problems with this database design?Think Codd: Independence? Consistency? Ease-of-Use? Change Sally Jones’s phone #?Add New Employee Bob Smith?

7. Activity: Relational Terminology Identify each of these : • Table • Relation • Row • Column • Tuple • Attribute • Atomic (Attrib.) • Logical Domain • Physical Domain

8. Tables are “Buckets for your data” “Products Appear on Orders” “Customers Place Orders” Orders • Tables: • Specialized • Order doesn’t matter • Contain real items • Relationships among tables: • Enforce business rules • Apply to real items Customers Products

9. What makes a table a relation? A column or a set of columns uniquely identify each row

10. DBMS : Physical Domain • Notes: • Different “flavors” of DBMSs use different data types. • It’s not part of the SQL Spec, but part of the DBMS implementation.

11. DBMS: Logical Domain / Constraints • 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. E.g., employee_hourly_wage>= 0 • Unique Constraint – ensures duplicate values are not inserted into a column. • Lookup table – a separate table containing all of the acceptable values for a given column.

12. Keys • Candidate Key – any attribute or combination of attributes that can uniquely identify each row • Primary Key - A candidate key which has been chosen by the database designer to uniquely identify each • Surrogate Key - A primary key whose values are automatically generated by the DBMS • Secondary Key - An attribute or combination of attributes used for row retrieval from a table. In practice, secondary keys are index candidates in the table design. • Foreign Key – an attribute or combination of attributes in one table whose values either match those of the primary key in another table or are null. Used to link relations.

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

14. The Primary Key Constraint • Special selected constraint (you choose it) • Enforces entity integrity on the table. • Must be data unique for each row • Should be a candidate key

15. Good PK … Bad PK Good candidate key choices? • Customer Name? • Email Address? • Name and DOB? • SSN? • Customer Selected value? • Random Unique #? • Sequential Unique #? • Last two are examples of surrogate keys • The best PK’s • Don’t change … ever! • Have no external meaning • Do not compromise security… • Do not hinder performance …

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

17. The Foreign Key constraint • The foreign key is a constraint on a column of one relation so that it can be associated with another relation. • Foreign keys must have referential integrity – their values must come from the corresponding Primary Key column in the relation.

18. Example of FK: The Lookup Table Foreign Key

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

20. One more time. Tables Rule for joining tables Columns:Physical Domain? Logical Domain? Allow Null? Keys: Candidate? Primary? Foreign, Surrogate, Secondary?

21. TheEqui-Join of PK-FK at Work EquiJoin

22. FK Example: Implementation of a 1-M Relationship

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

24. 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

25. 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

26. An Concrete Example • SU administration asks us to build a database for class enrollment • Conceptual model: represent requirements, what needs to be represent in the database? • E.g., student info, class info, a student enrolls in a class • Logicalmodel: what database model to use? Relational, object-oriented, object-relational, etc. • E.g., we use relational model (entities, relations). • Internalmodel: use a DBMS to implement our relational model • E.g., what do the student, class tables look like? • Externalmodel: what the internal model looks like to end users (hide details of the implementation, e.g., tables) • E.g., you can see a student enrolls in a class • Physical model: how database is implemented by the DBMS • E.g., how is the database physically stored? In a single file?

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

28. Alternatives • Fast prototyping • Agile development • eXtreme programming • Hackathon

29. Exercise • Design a database for on-campus parking info • Car • Parking lots • A parking permit allows a car to park in a certain parking lot • Tables and keys

30. Permits (table) Persons (table) Cars (table)