Chapter 5: Database Design

Spreadsheets

- Work well for simple to run businesses -Lead to potential problems during growth --DATABASES help

Relational Database Tables

-2D TABLES --COLUMNS store attributes or fields (vert) --ROWS store records (horiz) **One table per entity

Foreign Key

-A foreign key is an attribute that creates a link between two tables -A primary key of one table usually becomes a foreign key in another related table -Foreign keys provide a method for maintaining another type of integrity in the data called Referential integrity -Every relationship in a relational data model is supported by a foreign key

Primary Key

-Attribute that uniquely identifies a specific instance of an entity. Every entity in the database must have a primary key. -Primary keys provide a method for maintaining a type of integrity in the data called entity integrity -Primary key value must be unique for each instance of an entity

2 Important things to understand about databases

-ENTITY (Generalized category representing a person, place, thing, or event about which we store and maintain information) ***Supplier, part, invoice -ATTRIBUTES (Specific characteristics of each entity: ***SUPPLIER name, address, phone number, etc. ***PART number, description, unit price, etc. ***INVOICE number, date, amount

Main Vocab

-Entity (object or an event) -Attribute (characteristic of an entity) -Row or record (One specific instance of an entity) -Table (a collection of records) -Relational Database (collection of related tables) -DBMS (computer program that controls the creation, maintenance, and use of a database) RDBMS (Relational Database Management System)

Data

-Facts about current ACTIVITIES (date of sales invoice) -RESOURCES affected by the activities (Items sold) -PEOPLE who participate in activity (name or id of sales clerk, last four digits of customers credit card, etc.

Attribute

-Field -Column

Database Development

-First, create a logical representation called a data model -By learning how this all works, we can better guide the development effort -We will do some hands on practice on this

Organizing data

-If you were using paper, you would have ONE FILE for each instance of an ENTITY -In an IS, a database organizes the data in digital files ... we create one file (table) per entity

Master vs transaction files

-Master files record data about PEOPLE or THINGS (students, customers, cars, items, employees,etc.) -Transaction files record EVENTS (invoices, enrollments, shipments, reservations, etc.)

Business process organization

-Organized around two types of lists: **Master files **Transaction files

Database basics

-RECORDS store data for separate instances of an entity -FIELDS store data representing different attributes of an entity

Foreign Key

-Referential Integrity

Table

-Relation -List

Record

-Row -Tuple

Database purposes

-Store data -Provide an organizational structure for data -Provide a mechanism to interact with the data

OSU entities

-Students -Faculty Members -Courses -Buildings -IT equipment -Student enrollments -Pay slips -Fee receipts

Basics of Database Design

-Table is a group of records and is structured as a two dimensional grid of rows and columns -Each ROW in the table represents a record or a tuple -Each column represents a specific characteristic of an attribute

Business Processes and Data

-To effectively document a business process, data must be collected about three facets of the business process: --Each activity of interest --Resources affected by each activity --People who participate in each activity

Primary Key

-Unique ID

5 clues that you've outgrown excel

-Unmanageable spreadsheets (Databases can find buried info more quickly and elegantly) -Version-control Issues (One of the key advantages of databases over spreadsheets is that they can be shared with thousands of users over a single network) -Dirty Data (Databases restrict types of data) -Data redundancy (If youre entering same data into multiple spreadsheets) -Direct entry by customers (You would never ask customers to fill in the cells of a spreadsheet, but you would ask them to enter their information into an online form. That requires a database.)

VOCABULARY

1

Terminology Review

>>

Data

COMPUTER SIDE -Hardware (Actors) -Software (Instructions) HUMAN SIDE -People (Actors) -Procedures (Instructions) DATA (Bridge)

Whats a database?

Collection of related lists (tables) containing facts about people, places, things, or events.

Column =

Column = Attribute = field

Data Modeling / Database Design

Design process for a database can begin by making a list of the key transactions we want to record and the entities that participate in them

Foreign Key

Each record has a unique primary key but multiple records can have the same foreign key **Part_number is the primary key and supplier_number is the foreign key, enabling users to find related information from the supplier table about the supplier for each part

Relational Database

Most common form of a database

Entities

Person, place, or thing (master), or event (transaction) on which we store and maintain facts

Row =

Row = tuple = instance of an entity = record

Business process

Set of related, coordinated and structured activities that are usually performed by people and/or machines to accomplish a specific objective

Storing data in a structured way

Store invoices that a retailer gets from him vendors

Tables =

Table = entity = relation

Primary key

Uniquely id's each record and so can be used to retrieve or update any record **Supplier__number

Relational Databases

organize data into 2-D tables with rows and columns; RDBMS = Relational DBMS