1 / 50

Database Beginnings

Database Beginnings. CIS 121 – Computer Concepts II Instructor: Ron Christensen. What is Data?. Data are facts about things, places, events Facts about data are called metadata Data are building blocks for information. Common Data Applications.

sara-kent
Download Presentation

Database Beginnings

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 Beginnings CIS 121 – Computer Concepts II Instructor: Ron Christensen

  2. What is Data? • Data are facts about things, places, events • Facts about data are called metadata • Data are building blocks for information CIS 121 - Portland Community College

  3. Common Data Applications • Word Processors – Like fifth generation language, very difficult to track • Spreadsheets – Mostly two dimensional. When items change, multiple spreadsheets become problem some. • Databases – More efficient, real-time, on-line. CIS 121 - Portland Community College

  4. DBMS – Database Management System • DBMS software… • Manages metadata • Organizes data • Enforces security • Manages access to data and metadata • Enforces integrity CIS 121 - Portland Community College

  5. Figure 1.2 Information System Input Output Database CIS 121 - Portland Community College

  6. What is a Database (Black Box)? • A computer accepts input, processes it according to some rules, and makes output. • Everything will be represented as data, including the input and the output • So what the DBMS is doing is taking the data produced (the input) and converting it into information (the output) CIS 121 - Portland Community College

  7. Relational DBMS • Relational refers to the method of organizing data, i.e. in tables • Developed in the1970s • Most common of several different types of DBMS systems • Hierarchical • Network • Object CIS 121 - Portland Community College

  8. Microsoft ACCESS • Relational DBMS software for Windows • Available as part of MS-Office CIS 121 - Portland Community College

  9. Type of Databases • Production Database • Transaction oriented • Lots of well-defined read/write/update actions • Decision Support Database • Query oriented • Complex queries • Few updates or writes CIS 121 - Portland Community College

  10. A Bigger Picture… • Databases are a critical part of information systems • Information systems help people solve problems • Your personal database and web page will form the basis of a personal information system CIS 121 - Portland Community College

  11. Information System Figure 1.2 Software Operating System Application Programs DBMS Write and Use People Database Design and Manage Hardware CIS 121 - Portland Community College

  12. Entities • Entities are anything about which you want to know something • People, places, things, events, concepts • Data are facts about entities CIS 121 - Portland Community College

  13. Entity Sets • Entity sets are collections of relatedentities. Entities are related by their classification: • student entities are related by the fact that they are all students • invoice entities are related by the fact that they are all invoices • car entities are related by the fact that they are all cars CIS 121 - Portland Community College

  14. Entity Sets, cont. • Entity sets are named as a broad definition of the whole entity. • Entity and Entity set names are singular. • Entity and Entity set names are capitalized. Examples: An entity named HR contains HR entities. An entity named EMPLOYEE contains employee records. An entity named DEPARTMENT contains department records. CIS 121 - Portland Community College

  15. Entity Sets, cont. • Entity Sets can only contain related records • a STUDENT entity set may not contain INVOICE data • a DEPARTMENT entity set may not contain invoice data • a PRODUCT entity set may not contain employee data …. And so on CIS 121 - Portland Community College

  16. Relational Databases • Data is stored in TABLES (also called relational sets) • Tables are collections of attributes for related records • The reality is that lots of different things get stuffed into a table for a variety of reasons • (Tables are another word for entity sets) CIS 121 - Portland Community College

  17. Database Tables • A database table is used to store a record (or tuple) • An entity set is a collection of related records • An record is anything you want to keep track of, so an entity may be a person, place, thing, event, etc. • (Record is another word for Entity) CIS 121 - Portland Community College

  18. Database Table Components • At the conceptual level, a database table may be viewed as a matrix. • Matrix rows are also known as tuples or records • each row contains an record • Matrix columns are also called fields or attributes. • Each column (field) contains the record’s attribute values CIS 121 - Portland Community College

  19. Attributes • Attributes are the characteristics that describe records • A STUDENT record may be described by attributes that may include... • social security number • name • address • date of birth • major CIS 121 - Portland Community College

  20. Records, Attributes, Data • Records are things about which you want to know something, e.g. STUDENT • Attributes describe something about the entity, e.g. the name of the student • Data are the values of the attributes, e.g. DOE, JOHN CIS 121 - Portland Community College

  21. Null • A null is an absence of value • A null is NOT • A blank • A zero • A null has several meanings in a database • Value does not exist • Value is not known CIS 121 - Portland Community College

  22. Attribute Names • Attribute names are capitalized. • For documentation reasons, attribute names are composed of two parts: • the first few characters reflect the entity they help describe. • subsequent characters are sufficiently descriptive to identify the attribute. CIS 121 - Portland Community College

  23. Attribute Names, cont. • Examples of attribute names: EMP_LNAME = employee last name STU_GPA = student grade point average PROD_CODE = product code CUST_LNAME = customer last name INV_NUM = invoice number CIS 121 - Portland Community College

  24. Keys • Primary Key (PK) • an attribute (or combination of attributes) that uniquely identifies each row (tuple) in a table. • A PK composed of two or more attributes is known as a composite PK. • Foreign Key (FK) • an attribute in one table whose values match the PK values in a related table or whose “values” are null. • FKs are used to link (connect) related tables. CIS 121 - Portland Community College

  25. Data Integrity. • Entity Integrity • PK uniquely identifies each entity in a table • PK may not include nulls • Referential Integrity • FK values in one table match the PK values in the related table • FK may not include nulls CIS 121 - Portland Community College

  26. A Conceptual View of a Database Table Tables are named. The table you see here is the EMPLOYEE table. Each column contains the values of an attribute. The EMP_FNAME column only contains employee first names; the EMP_PHONE may only contain employee phone numbers. Named attributes (fields) Each row represents an record A row is also called a tuple. Each row/column intersection contains only one of an entity’s attribute values The Primary Key (PK) is a unique record identifier. If you know the PK value, you will know all of its row’s attribute values CIS 121 - Portland Community College

  27. Entity Integrity EMPLOYEE table A table exhibits entity integrity when all of its Primary Key (PK) values uniquely identify each table row (record.) 1. A PK cannot contain duplicate values 2. A PK cannot contains nulls Note: A null indicates the absence of a value; it is not a blank. (You create a null when you tap the ENTER key without first making an entry. A blank is created when you tap the space bar and then tap the ENTER key.) CIS 121 - Portland Community College

  28. Foreign keys (FK) and Referential Integrity A Foreign Key (FK) is an attribute located in one table that “points to” a Primary Key (PK) in a related table. The use of FKs allows you relate one table to another. To maintain referential integrity, a foreign key (FK) must reference an existing PK value in a related table it may be null. INVOICE table LINE table CIS 121 - Portland Community College

  29. Attribute types • Simple (atomic) • Composite • Single-valued • Multi-valued Derived CIS 121 - Portland Community College

  30. Simple (Atomic) Attributes • A simple (atomic) attribute cannot be decomposed into meaningful components • Examples: • The attribute EMP_LNAME cannot be decomposed, because you cannot subdivide EMP_LNAME into a set of new attributes. • The attribute PROD_PRICE cannot be decomposed, because you cannot subdivide PROD_PRICE into a new set of attributes. CIS 121 - Portland Community College

  31. Simple (Atomic) Attributes, cont. Simple attributes may be …. • single-valued or • multi-valued CIS 121 - Portland Community College

  32. Simple (Atomic) Attributes, cont. • Single-valued simple attributes • Example: an employee can have only one gender, so EMP_GENDER is a single-valued attribute. The attribute EMP_GENDER cannot be decomposed, so it is a simple attribute. • Multi-valued simple attributes • Example: an employee can have many degrees, so EMP_DEGREE is multi-valued. The attribute EMP_DEGREE cannot be decomposed, so it is a simple attribute. CIS 121 - Portland Community College

  33. Composite Attributes • A composite attribute can be decomposed into meaningful components • Example: an employee’s address, shown as 123 East Main Street, Nashville, TN 32123 may be decomposed into EMP_ADDRESS = 123 East Main Street EMP_CITY = Nashville EMP_STATE = TN EMP_ZIP = 32123 CIS 121 - Portland Community College

  34. Composite Attributes, cont. A composite attribute may be …. • single-valued or • multi-valued CIS 121 - Portland Community College

  35. Composite Attributes, cont. • single-valued composite attributes • Example: an employee can have only one date of birth, so EMP_DOB is single-valued. But the attribute EMP_DOB can be decomposed into year, month, and day, so it is a composite attribute. • multi-valued composite attributes • Example: an employee can have more than one address, so EMP_ADDRESS may be multi-valued. The attribute EMP_ADDRESS can be decomposed into street address, city, state, and ZIP code, so it is a composite attribute. CIS 121 - Portland Community College

  36. Attribute Storage • Each table row/column intersection contains a single attribute value for a single entity. • Ideally, attributes are simple and single-valued. • Single-valued composite attributes are acceptable • but composite attributes may make queries more complex and may impose reporting limitations. • Multi-valued attributes, either simple or composite, may • create structural problems • make queries more complex and may impose reporting limitations. • May be necessary for a variety of reasons, but should be avoided where possible CIS 121 - Portland Community College

  37. Multi-valued Attribute Storage • Multi-valued attributes are sometimes stored as • strings • this approach yields query complexity and reporting limitations • multiple attributes • this approach yields structural problems • Ideally, multi-valued attributes are handled through the use of composite tables. CIS 121 - Portland Community College

  38. Multi-valued Attributes Stored As Strings Multi-valued attribute Poor practice: Makes it difficult to generate queries such as “How many employees have earned BA or MBA degrees?” CIS 121 - Portland Community College

  39. Storing Multi-valued Attributes In Separate Columns Poor structure: many nulls table structure must be altered when additional degrees are earned CIS 121 - Portland Community College

  40. Composite Tables • Multi-valued attributes can generate many nulls, increasing uncertainty about the data • One solution is to add another table, called a composite table or bridge table • It is often better to use more tables rather than poor attributes in fewer tables CIS 121 - Portland Community College

  41. A Composite Table (EDUCATION) is Used To Convert M:N Relationships To 1:M Relationships Table name: EMPLOYEE Table name: EDUCATION Table name: DEGREE CIS 121 - Portland Community College

  42. Supertype/Subtype Relationships Are Used To Eliminate or Control the Occurrence of Nulls A table with many nulls Supertype. (Table name: EMPLOYEE Subtype. (Table name: PILOT) CIS 121 - Portland Community College

  43. Relationships • Tables can be related to each other in a variety of ways • 1:1 Relationships • 1:M Relationships • M:N Relationships • Recursive Relationships • Tables are related by their PKs and FKs CIS 121 - Portland Community College

  44. 1:1 Relationships • Entity in one table is related to only one entity in another table • Example • STUDENT : SOCIAL SECURITY NUMBER Each student has only one Social Security Number, each Social Security Number refers to only one student CIS 121 - Portland Community College

  45. 1:M Relationships • Entity in one table is related to many entities in another table • Example • STUDENT : TUITION INVOICE Each student may have several tuition invoices, each tuition invoice is assigned to only one student CIS 121 - Portland Community College

  46. M:N Relationships • An entity can occur more than once on both sides of the relationship – causes several different problems in databases • Example: • STUDENT : CLASS Each student may register for many classes, each class may contain many students CIS 121 - Portland Community College

  47. Some Issues with M:N • May signal redundant data, resulting in anomalies • Modification anomaly • Deletion anomaly • Keep it simple: DO NOT IMPLEMENT M:N RELATIONSHIPS. Use composite tables instead CIS 121 - Portland Community College

  48. Recursive Relationships • Entity is related to itself • Examples • TEAM : TEAM Teams in an athletic league play each other • CLASS : CLASS Some classes are pre-requisites for others CIS 121 - Portland Community College

  49. Optional (vs. Mandatory) Relationships • Optional relationships occur when entities in two tables may be related, but do not need to be. • Example (Optional Relationship): • STUDENT : SOCIAL SECURITY NUMBER A student is not required to have a social security number • Example (Mandatory Relationship) • STUDENT : CLASS A student must be registered in a class to be a student CIS 121 - Portland Community College

  50. End of Lecture CIS 121 - Portland Community College

More Related