Database beginnings
Download
1 / 50

Database Beginnings - PowerPoint PPT Presentation


  • 93 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Database Beginnings' - sara-kent


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Database beginnings

Database Beginnings

CIS 121 – Computer Concepts II

Instructor: Ron Christensen


What is data
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


Common data applications
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


Dbms database management system
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


Figure 1.2

Information System

Input

Output

Database

CIS 121 - Portland Community College


What is a database black box
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


Relational dbms
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


Microsoft access
Microsoft ACCESS

  • Relational DBMS software for Windows

  • Available as part of MS-Office

CIS 121 - Portland Community College


Type of databases
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


A bigger picture
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


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


Entities
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


Entity sets
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


Entity sets cont
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


Entity sets cont1
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


Relational databases
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


Database tables
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


Database table components
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


Attributes
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


Records attributes data
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


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


Attribute names
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


Attribute names cont
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


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


Data integrity
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


A conceptual view of a database table
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


Entity integrity
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


Foreign keys fk and referential integrity
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


Attribute types

  • Simple (atomic)

  • Composite

  • Single-valued

  • Multi-valued

    Derived

CIS 121 - Portland Community College


Simple atomic attributes
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


Simple atomic attributes cont
Simple (Atomic) Attributes, cont.

Simple attributes may be ….

  • single-valued

    or

  • multi-valued

CIS 121 - Portland Community College


Simple atomic attributes cont1
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


    Composite attributes
    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


    Composite attributes cont
    Composite Attributes, cont.

    A composite attribute may be ….

    • single-valued

      or

    • multi-valued

    CIS 121 - Portland Community College


    Composite attributes cont1
    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


    Attribute storage
    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


    Multi valued attribute storage
    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


    Multi valued attributes stored as strings
    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


    Storing multi valued attributes in separate columns
    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


    Composite tables
    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


    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


    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


    Relationships
    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


    1 1 relationships
    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


    1 m relationships
    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


    M n relationships
    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


    Some issues with m n
    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


    Recursive relationships
    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


    Optional vs mandatory relationships
    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


    End of lecture
    End of Lecture

    CIS 121 - Portland Community College


    ad