MIS chapter 5

Functional Dependency

- An attribute B is functionally dependent on another attribute A, if at any time the value of attribute A uniquely determines the value of attribute B. -B functionally depends on A if, knowing A, you can always determine the value of B

Primary Key (PK)

-A column or group of columns that identifies a unique row in a table. -Student Number is key of Student table. -Every table must have a key. -Sometimes more than one column is needed to form a unique identifier. In a table called City, key would consist of combination of City and State columns -The values of the PK must not repeat in the table (unique)

What Are the Components of a Database Application System?

-Applications make database data more accessible and useful -Users employ a database application that consists of forms, formatted reports, queries, and application programs -Database management system (DBMS) processes database tables for applications

Normalization

-One approach to create a DB design -It involves series of steps, which, if followed, will result in a fully normalized database

Types of relationships

1) 1-to-1 2) 1-to-many 3)many-to-one 4) many to many

Principles of database

1) Every table has a unique identifier (PK) 2) The value of PK cannot be empty 3) The order of records (rows) is not important 4) The order of columns (attributes) is not important

1NF-2NF-3NF out of

6NF but if you do only 3 NF everything else will be set up

Database

A collection of tables, relationships and metadata

Table or file, or relation

A group of records related by a common theme (very similar to a spreadsheet)

Foreign key

A non-primary key in one table that is related to a primary key of a different table

DBMS

A software program

Partial Dependency

A table suffers from partial dependency if it has non-key attribute(s), which is(are) functionally dependent on a portion of a combined primary key, but NOT on the entire combined primary key Therefore, if a table has simple primary key (consisting only of one column), then it by definition cannot have partial dependency

Transitive Dependency

A table suffers from transitive dependency if a non-key attribute C, is functionally dependent on a non-key attribute B, which, in turn, is functionally dependent on a key attribute A. In other words, a value in primary key column A value determines a value in a non-key column B, which, in turn, determines a value in another non-key column C.

Databases

Allow keeping track of multiple themes Examples: suppliers, purchase orders, shipments, inventory items, sales orders, customers, payments, etc.

R E-> A

ER diagram->tables shema->MS Access

Spreadsheets

Effective at keeping track of only a single theme Example: Student test scores in a course

Relation

Formal name for a table

Database has to have a

NUMBER is database that is unique

2NF

Note that on the previous slide we eliminated partial dependencies by moving columns which create this problem into new tables For example, since 'Cust_Code' and 'Cust_Name' depend on 'SO_Number', we moved 'Cust_Code' and 'Cust_Name' into a new table

In Janktion table we must have

PK's from from both tables as foreign key. The foreign keys form a combined PK

What Is a Database Management System (DBMS)? Database management system (DBMS)

Program that creates, processes, and administers databases Examples: DB2, Microsoft Access, SQL Server, Oracle, MySQL (open-source DBMS)

RDBMS=

Relational Database Management System

Relation databases

Relationships among tables are created by using foreign keys

How many steps in the normalization?

There are six of them, but most businesses find that normalizing to the third form will create a good database design

What is the purpose of a Database?

To organize and keep track of things

Every table has

a primary key

Normalization also called

bottom-up approach and the idea is to take a standard business paper form, used in day-to-day business and try to design data storage capable of storing each piece of data uniquely This approach essentially mimics existing business paper forms, creating an electronic storage for data

Many-to-Many

cannot exist in databases

Multiple columns combined

combined primary key

Steps in normalization called

database normal forms

We need to avoid

duplication

Second Normal Form (2NF)

if a table is in 1NF, and has no partial dependencies.

Universal data base rule

in one-to-many(1-to-m) or many-to-one relationship the primary key from 1 end of the relationship is inserted into many end as a foreign key

we have to separate

information (some information is specific)

Database design

involves deciding what tables the database will have, and how they will be related to each other

The objective of the database design

is to have tables, so that each piece of data is recorded in the database only once --This will allow to avoid duplications and inconsistencies

Foreign key suppose or not supposed to be unique?

no

Simple primary key

one column primary key

1NF-each cell in the table must contain

only one value

Components of a database

tables or files+Relationship among rows in tables+metadata

Partial dependency happens when

you have a combine PK