Database System Components

1. Database System Components The Database and the DBMS

2. Bridge Machine Human Hardware Programs Data Procedures People Instructions Actors A Model of the Database Processing System

3. Database System Components

4. User Data • Data about • Entities or objects • employees, departments, products.... • students, courses, professors • Attributes • SS#, name, address, salary, department • Relationships (bi-directional) between entities or objects • Employees assigned to Departments. • Departments are assigned Employees. • Students take Courses. • Courses are taken by Students.

5. Movie Rating Star Saving Private Ryan G Tom Hanks Saving Private Ryan G Matt Damon Titanic R Kate Winslet Titanic R Leonardo Di Caprio Users’ Data • For relational databases, tables with rows and columns representing unique records and attributes • Not all tables are equally desirable: What is the problem with this?

6. Relationships Between Entities • Cardinality • the number of entities of each type that may participate in the relationship • may be • 1:1 (one-to-one) • 1:M (one-to-many) • M:N (many-to-many) • to determine cardinality, ask: • for each A, how many B’s may be related? • for each B, how many A’s may be related?

7. Minimum & Maximum Cardinality • For each A, what is the minimum and maximum number of related B’s. • For each B, what is the minimum and maximum number of related A’s. • If the minimum is zero • The relationship is “optional.” • The entity may or may not (sometimes) participates • One or more • The relationship is “mandatory.The entity must (always) participate.

8. Relationship Examples • Business Rules • 1 : 1 (one-to-one) • Each faculty member is always assigned to one office. • Each office is sometimes assigned to one faculty member. • 1 : M (one-to-many) • Each employee is always assigned to one department. • Each department is always assigned many employees. • M : N (many-to-many) • Each student always takes many courses. • Each course is sometimes taken by many students

9. What is the cardinality between... • Cities and zip codes? • Each city ? has ? zip codes. • Each zip code ? corresponds to only ? city. ===> 1:M • People and phone numbers? • Each person ? has ? phone numbers. • Each phone number ? belongs to ? persons. ===> M:N

10. Four Data “Models” • Hierarchical • Network • Relational • Object Oriented

11. Hierarchical • Support 1:1 and 1:M Relationships • Parent - Child Viewpoint • Parent (Owner) record has one or more children (Members) • Customer has one checking account • Checking account has many transactions • Each child (member) has only one parent (owner)

12. Example Hierarchy: Banking Database Customer# | SS# | Address| ... Checking Acct. # | Current Balance | Statement Date Check# | Date | Payee | Amount Deposit# | Date | Amount Savings Acct. # | Current Balance | Statement Date

13. Simple Network • Children may have more than one parent • Example: Joint checking account John Dough Jayne Dough Checking Acct. # 999

14. Complex Network • Supports M:N relationships Students Portfolios Courses Securities

15. Relational Model • Data organized into tables (relations) • Tables have columns (attributes) • Data stored in rows (tuples) • Each row has a unique primarykey • Relationships included in the data

16. Relational Tables and Data EMPLOYEE(SS#, EmployeeName, EmployeeAddress, Department_ID) DEPARTMENT(DEPARTMENT_ID, DepartmentName, Manager) EMPLOYEE SS# EmployeeName Employee Address Department_ID 1223 John Dough Wall Street Acct 3214 Jayne Dough Wall Street Fin 7653 Jill Outley Wellborn Acct DEPARTMENT DEPARTMENT_ID DepartmentName Manager Acct Accounting Beane Kounter Fin Finance Cash McCall

17. Metadata • “Data about the data.” • Data dictionary - Data Definition Language • For each table or record • Table or record name • Number of columns or Fields • Primary key • For each data item • Date item name • Data type (Numeric, date, text...) • Size

18. Metadata • Use DDL • Description of database structure • System tables (e.g., tables and columns)

19. Indexes • Usually “inverted lists” for accessing data. Department Employees Acct 1223, 7653 Fin 3214

20. Indexes and other data structures • Improve performance for sorting and searching • But: overhead of updating • Who has Info Systems as major?

21. Application Metadata • Structure & format of application components • Forms • Reports • Queries • Etc.

22. Forms • Data entry • Hiding IDs

23. Queries • By example (QBE) • By form • By DML (Data Manipulation Language)

24. Reports • Data display

25. Menus • Organize application components

26. Application programs • DMLs • Standard languages + DBMS interface • Eg: trapping events in Visual Basic + Access

27. DBSystemArchitecture

28. The Database Schema • Tables • Relationships • Domains • constraints on values that a column may have. • e.g. date, integer number, text of length N... • ID’s (primary keys) must be unique • Business rules • Rules or policies that must be enforced in the database. • Each employee is always assigned to one department. • Each department is always assigned one or more employees.

29. Summary • Database Systems include • the database consisting of • Data, Metadata, Indexes, Application metadata • the database management system (DBMS) • Design Tools & Run Time Tools