1 / 29

Database System Components

Database System Components. The Database and the DBMS. Bridge. Machine. Human. Hardware. Programs. Data. Procedures. People. Instructions. Actors. A Model of the Database Processing System. Database System Components. User Data. Data about Entities or objects

gail-porter
Download Presentation

Database System Components

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

More Related