Database Management Systems & Programming

1. Database Management Systems & Programming LIS 558 - Week 7 Relational Database Implementation Faculty of Information & Media Studies Summer 2000

2. Lecture Outline • Assignment #1 • Quiz • Half-way Summary & Review • Break • Database Implementation & Demonstration • Events & Windows Interface

3. 1 Customer (1,1) places (1,1) (1,N) (0,N) (0,N) (1,1) Catalogue Items Order Items Order M 1 M M 1 Assignment #1 Customer (CID, Lname, Fname, Phone, Address, City, Province, CCardType, CCardNumber...) Order (Ord#,C_ID, OrdDate, ShipDate…) Order-Item (CatID, Ord# , NumOrdered, Price, ) ItemCatalogue (CatID, Description, Price, NumInStock)

4. 1 M Order Customer places (1,1) 1 (0,N) (1,N) includes item M (1,1) Order Item Catalogue taken from (0,N) (1,1) 1 M Assignment #1 Customer (CID, Lname, Fname, Phone, Address, City, Province, CCardType, CCardNumber...) Order (Ord#,C_ID, OrdDate, ShipDate…) Order-Item (OrderItemID, Ord#, CatID, NumOrdered, Price) ItemCatalogue (CatID, Description, Price, NumInStock, ...)

5. Content of LIS558 Practical • database basics & terminology • database schemas • constructing tables • editing and maintaining databases • queries and filters • sorting • creating forms • developing reports • customizing forms and reports • user interface development Theoretical • fundamental concepts • overview of data processing • data requirements • design methodology • data modeling techniques • problems in database design • normalization • basics of SQL • relational database implementation • application design and user interface issues • transaction management • multi-user/client-server databases • web architecture Halfway Point of Course!

6. Seven Step Conceptual Design Methodology • Prepare statement of purpose and define database objectives • Analyze current database (e.g., reports, forms, etc.), assess user requirements, and create data model • Create data structures (tables, fields, field specifications, establish keys) • interview transcripts • meeting minutes • observational notes • document analyses • business mission and strategy statements • questionnaire results • business forms • training manuals • flow charts • consultant reports • reports • job descriptions • computer generated output

7. Seven Step Conceptual Design Methodology • Determine table relationships • Define and determine business rules critical to database design (e.g., required fields, validation rules) • Determine and establish user views of data • Review data integrity and reiterate design methodology

8. 1 : 1 Relationship Type 1 : N Entities Maximum Cardinality M : N Mandatory Degree Objects Minimum Cardinality Relationships Recursive Optional Binary Domains Attributes Ternary Values N-ary E-R Modeling Concepts

9. Entity-Relationship Design • Top-down design approach - develop a list of entities and attributes that are of interest to the enterprise • E-R Design consists of determining entities, attributes, relationships, relationship types, level of participation in relationships, identifiers, and then drawing an E-R Diagram • E-R Diagram (model) is a blueprint from which a well-structured database is created

10. Steps to E-R Transformation 1. Identify entity types 2. Identify relationships 3. Determine relationship type 4. Determine level of participation 5. Assign an identifier for each entity 6. Draw completed E-R diagram 7. Deduce a set of preliminary skeleton tables along with a proposed primary key for each table (using seven rules) 8. Develop a list of all attributes of interest (not already listed and systematically assign each to a table in such a way to achieve a 3NF design (i.e., no repeating groups, no partial dependencies, and no transitive dependencies

11. Database Design Problems • Numerous structural problems can arise • Redundancy • Multi-valued problems • Update anomalies • Insertion anomalies • Deletion anomalies

12. Rules for Normalization • Bottom-up design approach or used as a method to check E-R model 1NF - remove repeating values 2NF - remove partial dependencies 3NF - remove transitive dependencies • For most database designs 3NF is sufficient

13. Implementation Outline • Maintaining Data Consistency & Integrity • Understanding Table Joins • Access Query Types • More SQL • Windows & Events • Building the User Interface

14. Steps to Relational Implementation 1. Define the database structure to the DBMS 2. Allocate Media Space 3. Create/enter the Database Data

15. Guidelines for Ideal Table Design 1. Each table should represents a single theme or subject 2. Tables should include primary keys that uniquely identify each record of each table 3. Avoid the use of smart keys that attempt to embed meaning into primary keys (keys should be meaningless) 4. The domain of primary keys should be large enough to accommodate the identification of unique rows for the entire potential universe of records 5. A primary key should be a unique, random or sequential collection of alphabetic, numeric or alphanumeric characters 6. Use the suffix ID in constructing primary keys to ensure they are readily identifiable. 7. Tables should not contain multipart fields 8. Tables should not contain multivalued fields 9. Tables should not contain calculated or derived fields 10. Tables should not contain unnecessary duplicate fields 11. There should be a minimum amount of redundant data

16. Database Definition Revisited • Database Definition • A collection of data, organized logically and managed by a unifying set of principles, procedures, and functionalities, which helps guarantee the consistent application and interpretation of that data • 1st half - considered methodology for achieving logical organization of data • Now we’ll look at mechanisms for ensuring data quality

17. Relational Database Management Systems • Provide mechanisms for minimizing data entry error and inconsistency • How is data quality, consistency, and integrity maintained in Microsoft AccessTM: • formats & input masks • validation rules • validation text • status line descriptions • lookup lists and tables • use of referential integrity • database, table, and field properties • Examine these features by example

18. PATRON M:N BOOK BORROW PatronID, ... Call Number, ... Simple Circulation System E-R Model • Binary Relationship • Many-to-Many type • Optional level of participation on both sides of relationship • PatronID and CallNumber are entity identifiers From CASE # 5 Patron (PatronID, PName, PPhone) Book (CallNumber, Title, Author) Borrow (PatronID, CallNumber)

19. Access ‘97 Implementation of Model

20. Database Properties

21. Database Properties

22. Database Options

23. Database Options

24. Database Startup Options

25. Database Startup Properties

26. Implementation of Circulation System Model

27. Database Object Prefixes

28. Call Number Field Properties

29. Title Field Properties

30. Author Field Properties

31. Year Field Properties

32. Table Properties

33. Table Properties

34. Selecting Data Type

35. Formats & Input Masks

36. Formats & Input Masks

37. Formats & Input Masks

38. Formats & Input Masks

39. Formats & Input Masks

40. Field Validation

41. Field Validation

42. Author Field Properties Be cautious when using required property

43. Lookup Lists & Tables

44. Lookup Lists & Tables

45. Lookup Lists & Tables

46. Lookup Lists & Tables

47. Referential Integrity

48. Referential Integrity

49. Referential Integrity • Referential integrity is a mechanism that enforces the ties between data in separate tables and prevents them from being broken • Referential integrity minimizes the undesirable likelihood of the existence of a record in the child table for which there is no corresponding record in the parent table - referred to as an orphan (or dangling) record • Prior to setting referential integrity, ensure that • the field used to tie two tables together (the link field) must be a primary key field in the parent table and a foreign key in the child table • the link fields have an identical data type • the two tables are in the same database container

50. A value cannot be entered in the foreign key field of the related table if that value doesn't exist in the primary key of the parent table. A Null value in the foreign key is allowed, specifying that the records are unrelated. Referential Integrity in Access ’97 • A record cannot be deleted from a parent table if matching records exist in a related table. • A primary key value in the parent table cannot be changed, if that record has related records. Determined by MS Access on the basis of primary key settings.