MIS- Chapter 1

Sometimes the primary key can be made up of several fields:

(LicensePlate, State) is a possible primary key. - Since it is constituted by two attributes, we call it a composite key

More on primary keys

- a primary key should be minimal: it should not contain unnecessary attributes - sometimes we may invent a new attribute to serve as a primary key (sometimes called a synthetic key) --> if no suitable primary key is available --> or to avoid composite keys --> in Access, "Autonumber" fields can serve this purpose

Tables (relations) properties

- the table's name is unique within the database - the roles of columns and rows are not interchangeable - each column has a unique attribute name (within the table) - all entries in a column have the same data type - each table must have a primary key - each row is unique (no duplicates) - each cell contains atomic data: no lists or sub-tables - row order and column order are unimportant

Advantages of using a DB:

1. Data is organized accordingly to the relationships that exist within 2. Control of data redundancy 3. Reduces the risk of inconsistencies

Guidelines to design a table

1. Include all the attributes you are likely to need in the future. 2. Separate an attribute into two if you will need part of it in the future (keep in mind the key words: atomic data) --> E.g. separate last name from first name so we can sort easily students by last name in the future. 3. Avoid calculated fields (redundant data) --> E.g. storing the GPA is not necessary since it can be obtained from two already stored attributes: total credits and amassed grade points

Disadvantages of using a DB:

1. It's complex: users must learn and understand basic database concepts. 2. More expensive: software cost, cost of training staff to use the system, cost of hiring staff to maintain the system.... 3. A failure can have a very high impact. The centralization of resources increases the vulnerability.

DB Rule #1- Entity integrity

Each record must have a non-empty primary key.

Databases

It is a repository of logically related data designed to meet the information needs of an organization. - not about data only, but about the relationships too.

MS Access

It is an example of a Database Management System

Relational Model

The most widely used "kind" of databases.

Each record of a table is unique. How do we express that uniqueness?

We create a primary key: an attribute or a collection of attributes whose value(s) uniquely identify each row in a relation.

Database Management System

a piece of software designed to store, manage, and interact with databases and its applications. - provides a wide range of other services necessary to the database like recovery from crashes, quick application development, integrity of the data, security,....

Columns

are called attributes or fields. - Individual properties broken into the smallest components possible (e.g. LastName, Address, PhoneNumber)

Rows

are called records or tuples. - Each record corresponds to one entity (e.g. a single person).

Personal DBMS products

are designed for smaller, simpler database applications. - Mostly used for personal or small workgroup applications that involve fewer than 100 users. --> Example: Microsoft Access

In a relational database...

data is organized into one or more relations (tables). - Each relation corresponds to a single "object", e.g. customers and orders are two different objects, though they are related, they should be stored in two different tables.

Database design

given an application, which entities to create? How do we represent the relationships?

Primary key

is a minimal set of attributes (column(s)) that together uniquely identify each record (row); - e.g. in a table with students, we may have an attribute for the student ID, which uniquely identifies each student.

A table can have ____ primary key.

one

Each table must have a field called.....

primary key

Enterprise DBMS products

process large organizational and workgroup databases. - They support many, possibly thousands, of users and many different database applications. - Able to manage databases with petabytes (1PB=1million GB) or more. - Security: Different users, different roles - Performance: Need to provide concurrent access - Consistency: Concurrency can lead to update problems --> Examples: MySQL, IBM's Microsoft's SQL Server, and Oracle's Oracle DB.