database beginnings cis 121 – computer concepts ii instructor: ron christensen
TRANSCRIPT
Database Beginnings
CIS 121 – Computer Concepts II
Instructor: Ron Christensen
04/20/23 CIS 121 - Portland Community College
2
What is Data?
• Data are facts about things, places, events
• Facts about data are called metadata
• Data are building blocks for information
04/20/23 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.
04/20/23 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
04/20/23 CIS 121 - Portland Community College
5
Database
Information System
Figure 1.2
Input Output
04/20/23 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)
04/20/23 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
04/20/23 CIS 121 - Portland Community College
8
Microsoft ACCESS
• Relational DBMS software for Windows
• Available as part of MS-Office
04/20/23 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
04/20/23 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
04/20/23 CIS 121 - Portland Community College
11
Database
DBMS
Operating System
Software
Hardware
People
ApplicationPrograms
WriteandUse
Designand
Manage
Information SystemFigure 1.2
04/20/23 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
04/20/23 CIS 121 - Portland Community College
13
Entity Sets
• Entity sets are collections of related entities. 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
04/20/23 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.
04/20/23 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
04/20/23 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)
04/20/23 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)
04/20/23 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
04/20/23 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
04/20/23 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
04/20/23 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
04/20/23 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.
04/20/23 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
04/20/23 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.
04/20/23 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
04/20/23 CIS 121 - Portland Community College
26
A Conceptual View of a Database Table
Named attributes (fields)
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
Each row representsan record
A row is also calleda tuple.
Each row/columnintersection containsonly one of an entity’s attribute values
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.
Tables are named. The table yousee here is the EMPLOYEE table.
04/20/23 CIS 121 - Portland Community College
27
Entity Integrity
A table exhibits entity integrity when all of itsPrimary Key (PK) values uniquely identify eachtable row (record.)
EMPLOYEE table
1. A PK cannot contain duplicate values2. 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.)
04/20/23 CIS 121 - Portland Community College
28
Foreign keys (FK) and Referential Integrity
To maintain referential integrity, a foreign key (FK) must reference an existing PK value in a related table it may be null.
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.
INVOICE table
LINE table
04/20/23 CIS 121 - Portland Community College
29
1. Simple (atomic)
2. Composite
A. Single-valued
B. Multi-valued
Derived
Attribute types
04/20/23 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.
04/20/23 CIS 121 - Portland Community College
31
Simple (Atomic) Attributes, cont.
Simple attributes may be ….
• single-valued
or
• multi-valued
04/20/23 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.
04/20/23 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
04/20/23 CIS 121 - Portland Community College
34
Composite Attributes, cont.
A composite attribute may be ….
• single-valued
or
• multi-valued
04/20/23 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.
04/20/23 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
04/20/23 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.
04/20/23 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?”
04/20/23 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
04/20/23 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
04/20/23 CIS 121 - Portland Community College
41
Table name: DEGREE
Table name:EMPLOYEE
Table name:EDUCATION
A Composite Table (EDUCATION) is UsedTo Convert M:N Relationships To 1:M Relationships
04/20/23 CIS 121 - Portland Community College
42
Supertype/Subtype Relationships Are UsedTo Eliminate or Control the Occurrence of Nulls
A table with many nulls
Subtype. (Table name: PILOT)
Supertype. (Table name: EMPLOYEE
04/20/23 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
04/20/23 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
04/20/23 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
04/20/23 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
04/20/23 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
04/20/23 CIS 121 - Portland Community College
48
Recursive Relationships
• Entity is related to itself
• Examples– TEAM : TEAMTeams in an athletic league play each other
– CLASS : CLASSSome classes are pre-requisites for others
04/20/23 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 NUMBERA student is not required to have a social security number
• Example (Mandatory Relationship)– STUDENT : CLASSA student must be registered in a class to be a student
04/20/23 CIS 121 - Portland Community College
50
End of Lecture