IS 201: Database Design (DBMS) | Midterm

What ways Database to store data?

• Many filters/constraints available to protect data accuracy.

• Very flexible. Data entered once can be viewed in many different formats.

• Can do calculations, but only basic math.

• Difficult to learn

• Must plan/design the database in advance.

What ways Spreadsheet (Excel) to store data?

• Completely dependent on user for accurate input.

• Can make small color changes to basic spreadsheet.

• Statistics, financial functions available.

• Easy to learn

• Not much planning required.

Why do organizations usually store data in a database?

• Protect the “integrity” of the data: Make data accurate & reduce data redundancy.

• Make data more accessible: Enhance flexibility of data access methods & improve speed of data access.

• To make an application more adaptable: Provide more flexibility in application development & decrease dependence on a given visualization method.

What are the goals of database design?

• Data is not repeated in more than one…

  • entity.

  • entity instance.

  • column.

Occurs when the same information is unnecessarily stored multiple times in a database.

Data Redundancy

• A graphical representation of a database structure that shows how entities are related to each other.

• Contains three structures: Entity, Attribute, & Relationship

ER (Entity-Relationship) diagram

An organized collection of structured information stored electronically.

Database

A set of software that facilitates the storage and access of data on a computer.

Database Management System (DBMS)

An individual unit in an electronic document or spreadsheet where data is stored.

Cell

A set of related data stored in rows and columns.

Table

An object of interest (ex: Customer or Product)

Entity

A characteristic describing an entity (Ex: CustomerName)

Attribute

An association between entities (Ex: Customer purchasing a Product)

Relationship

A named data item in a database table (Ex: FirstName)

Field

A vertical division in a table or spreadsheet representing attributes or fields.

Column

A horizontal division in a table or spreadsheet representing a single record or entity instance (tuple).

Row

The primary key of one table stored in another table to link them together.

Foreign Key

A specific factual measurement or entry contained in data.

Data Value

A specification that determines how data is stored and what operations can be performed on it (Ex: Short Text, Number, Currency).

Data Type

A relationship where each instance of entity A can relate to multiple instances of entity B, and each instance of entity B can relate to multiple instances of entity A (Ex: a customer can buy multiple books, and a book can be bought by multiple customers)

Many-to-many relationship

A many-to-many relationship is implemented by creating a new intersection (or junction) table and storing the primary keys from both related tables as foreign keys within it (often forming a composite primary key).

Implementation

In examples of a 1:M (one‐to‐many) relationship, where will the foreign key be stored?

"MANY" table (the child table)

• The selected identifier that best uniquely identify an entity instance.

• It must be unique, never changing, and non-null.

Primary Key

• A meaningful value that already exists naturally in the data

• It is a good choice if it will never be used as a foreign key.

(Ex: a combination of name, address, and birthday.)

Natural Key

• Generate automatically and has no intrinsic meaning

• Have no business rule meaning

• Ideal for foreign key relationships.

Artificial Key

Consists of rules and properties applied to fields, designed to protect data accuracy, consistency, and integrity.

database constraint

• A set of rules that ensures the consistency and accuracy of data when using foreign keys to establish relationships between tables.

• Any value of a foreign key must exist in the referenced table or be NULL

• Without it, a record may be an orphan

referential integrity