1 / 58

Chapter 1 Overview of Database Concepts

Chapter 1 Overview of Database Concepts. Chapter Objectives. Identify the purpose of a database management system (DBMS) Distinguish a field from a record and a column from a row Identify the basic components of an Entity-Relationship Model

lenora
Download Presentation

Chapter 1 Overview of Database Concepts

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. Chapter 1Overview of Database Concepts Oracle9i: SQL

  2. Chapter Objectives • Identify the purpose of a database management system (DBMS) • Distinguish a field from a record and a column from a row • Identify the basic components of an Entity-Relationship Model • Define the three types of relationships that can exist between entities Oracle9i: SQL

  3. Chapter Objectives • Explain the purpose of normalization • Describe the role of a primary key • Identify partial dependency and transitive dependency in the normalization process Oracle9i: SQL

  4. Chapter Objectives • Explain the purpose of a foreign key • Determine how to link data in different tables through the use of a common field • Explain the purpose of a structured query language (SQL) Oracle9i: SQL

  5. Database Terminology • Database – logical structure to store data • Database Management System (DBMS) – software used to create and interact with the database Oracle9i: SQL

  6. Database Components • Bit (0 or 1) is the smallest data unit. • A few bits (usually 8) can be organized into a byte. • Each byte represents a character that is the basic building block of information. • Field • Record • File Oracle9i: SQL

  7. Database Components - Character • Basic unit of data • Can be a letter, number, or special symbol Oracle9i: SQL

  8. Database Components - Field • A group of related characters • Represents an attribute or characteristic of an entity • Corresponds to a column in the physical database Oracle9i: SQL

  9. Database Components - Record • A collection of fields for one specific entity • Corresponds to a row in the physical database Oracle9i: SQL

  10. Database Components - File • A group of records about the same type of entity Oracle9i: SQL

  11. What Are Relations • A Relational DB is a collection of relations • A relation is a two-dimensional table that has the following characteristics • Rows contain data about an entity • Columns contain data about attributes of the entity • Cells of the table hold a single value • Each column has a unique name • The order of the columns is unimportant • The order of the rows is unimportant • No two rows may be identical Oracle9i: SQL

  12. What Are Relations • Interchangeably Used Terms in Relational Database • table = file = relation • row = record= tuple • column = field = attribute Oracle9i: SQL

  13. Components Example Oracle9i: SQL

  14. The Customers File/Table/Relation • CUSTOMERS Oracle9i: SQL

  15. Review of Database Design • Systems Development Life Cycle (SDLC) • Entity-Relationship Model (E-R Model) • Normalization Oracle9i: SQL

  16. Systems Development Life Cycle (SDLC) 5 Phases • Systems investigation – understanding the problem • Systems analysis – understanding the solution • Systems design – creating the logical and physical components Oracle9i: SQL

  17. Systems Development Life Cycle (SDLC) • Systems implementation – placing completed system into operation • Systems maintenance and review – evaluating the implemented system Oracle9i: SQL

  18. Entity-Relationship Model (E-R Model) • Used to depict the relationship that exists among entities. • Entity Definitions • An object of interest to the business • A class or category of thing • A named thing or a thing of significance about which the business needs information • Entity instance is the representation of a particular entity. Attribute is a characteristic of an entity. Oracle9i: SQL

  19. Entities: Examples INSTRUCTOR STUDENT COURSE code name fee length name phone no. name phone no Oracle9i: SQL

  20. E-R Model Symbols Oracle9i: SQL

  21. enrolled in COURSE STUDENT taken by Relationships: An Example Each STUDENT may be enrolled in one or more COURSES Each COURSE may be taken by one or more STUDENTS Oracle9i: SQL

  22. Relationships • The following relationships can be included in an E-R Model: • One-to-one • One-to-many • Many-to-many Oracle9i: SQL

  23. One-to-one Relationship • Each occurrence of data in one entity is represented by only one occurrence of data in the other entity • Example: Each individual has just one Social Security Number (SSN) and each SSN is assigned to just one person Oracle9i: SQL

  24. One-to-many Relationship • Each occurrence of data in one entity can be represented by many occurrences of the data in the other entity • Example: A class has only one instructor, but each instructor can teach many classes Oracle9i: SQL

  25. Many-to-many Relationship • Data can have multiple occurrences in both entities • Example: A student can take many classes and each class is composed of many students • Can not be included in the physical database Oracle9i: SQL

  26. Relationship Types: More Examples Many-to-One Many-to-Many One-to-One Oracle9i: SQL

  27. Many-to-One Relationships visited by CUSTOMER SALES REPRESENTATIVE assigned to Oracle9i: SQL

  28. Many-to-Many Relationships attended by PATIENT HEALTH CARE WORKER assigned to Oracle9i: SQL

  29. One-to-One Relationships is ridden by BICYCLE RIDER the rider Oracle9i: SQL

  30. Entity-Relationship Model • An entity is usually represented as a square or rectangle. • A line between two entities depict their relationships (a dashed line for optional relationship) • One-to-one: straight line • One-to-many: a straight line with a “crowfoot” at the “many” end. • Many-to-many: a straight line with a “crowfoot” at each end. Oracle9i: SQL

  31. Example E-R Model Oracle9i: SQL

  32. Normalization • Determines required tables and columns for each table • Multi-step process. Application of a series of rules that gradually improve the design • Used to reduce or control data redundancy Oracle9i: SQL

  33. Unnormalized Data Contains repeating groups in the Author column in the BOOKS table Oracle9i: SQL

  34. First-Normal Form (1NF) • Primary key is identified • Primary key is a field or a set of fields (composite PK) that serve to uniquely identify each record in a table. e.g. the SSN field in a table that contains students’ record. • Repeating groups are eliminated. Single valued attributes only Oracle9i: SQL

  35. Primary Keys • Primary key • Value must be unique for each record • Serves to identify the record • Present in every record • Can’t be NULL • Usually numeric Oracle9i: SQL

  36. Candidate Keys • Candidate key • Any field that could be used as the primary key • Should be a unique, unchanging numeric field Oracle9i: SQL

  37. Surrogate Keys • Surrogate key: created to be the record’s primary key identifier when no suitable primary key exists • Surrogate key has no real relationship to the record to which it is assigned, other than to identify the record uniquely • Developers configure the database to generate surrogate key values automatically • Surrogate keys are always numerical fields Oracle9i: SQL

  38. Foreign Keys • Foreign key:a field in a table that is a primary key in another table • Foreign key creates a relationship between the two tables • Foreign key value must exist in the table where it is a primary key Oracle9i: SQL

  39. Oracle9i: SQL

  40. Composite Keys • Composite key: a unique key that you create by combining two or more fields • Usually comprised of fields that are primary keys in other tables Oracle9i: SQL

  41. First-Normal Form (1NF) ISBN and Author columns together create a composite primary key Oracle9i: SQL

  42. Composite Key-P.D. • More than one column is required to uniquely identify a row • Can lead to partial dependency - a column is only dependent on a portion of the primary key Oracle9i: SQL

  43. Second-Normal Form (2NF) • Partial dependency must be eliminated • Break the composite primary key into two parts, each part representing a separate table Oracle9i: SQL

  44. Second-Normal Form (2NF) BOOKS table in 2NF Oracle9i: SQL

  45. Third-Normal Form (3NF) Publisher contact name has been removed Oracle9i: SQL

  46. Summary of Normalization Steps • 1NF: eliminate repeating groups, identify primary key • 2NF: table is in 1NF and partial dependencies eliminated • 3NF: table is in 2NF and transitive dependencies eliminated Oracle9i: SQL

  47. Linking Tables • Once tables are normalized, make certain tables are linked • Tables are linked through a common field • A common field is usually a primary key in one table and a foreign key in the other table • A foreign key is a column that is a primary key of another table Oracle9i: SQL

  48. Linking Tables • For every value of a foreign key there is a primary key with that value • A foreign key can never be null • A primary key must exist before the foreign key can be defined Oracle9i: SQL

  49. Oracle9i: SQL

  50. JustLee Books’ Database Assumptions • No back orders or partial shipments • Only US addresses • Shipped orders are purged (deleted) at the end of the month Oracle9i: SQL

More Related