MGS 351 Exam #2 (Lecture 4)

What are the design principals?

1) Must be a business expert - know how the business operates 2) The relational design needs to be strong 3) Proper design accurately reflects organizational business rules (ex. policies with customers) 4) Go against rules to make things simple.

How do you denote a one to one relationship?

1:1

How do you denote a one to many relationship?

1:N

What is an ERD?

A diagramming tool used to design a relational database (blueprint for database)

What is an example of a many-to-many relationship?

A single student can be enrolled in multiple courses, and a course can have multiple students in it.

What is an example of a one-to-many relationship?

A single student can borrow many library books. A single book, however, can only be borrowed by one student at a time.

If an ERD is designed properly, what should it do?

Accurately reflect the business rules of the organization. Visual illustration of the organization's business rules. Therefore, a database is just a reflection of an organization's business rules.

How do you build a N:M relationship database?

Add an intersection table, and flip all of the many sides of the relationship line so they are facing the intersection table. Take the primary keys from the original tables and add them to the new intersection table.

If a database must be simplified into a N:M relationship, how is it done?

Add intersection table which includes primary keys from both tables

If a database must be simplified into a 1:N relationship, how is it done?

Add primary key from the 'one' entity as a foreign key in the 'many' entity. For example: single student library card would be the primary key added as a foreign key to the book entity which has a primary key of book id (multiple books)

What is data normalization?

Art and science of properly designing data bases

What is a key attribute?

Attribute that uniquely identifies an entity. Primary key.

For intersection tables, what is recommended for a primary key?

Autonumber because it is easier to combine into one field rather than using two fields into on ID.

What is recommended in database normalization?

Avoid common database problems, make the data as tightly bound as possible, STORE MINIMUM amount of data

What is an example of a one to one relationship question?

Can a single loan be related to one or many customers?

How should a database design look?

Conceptual and logical. The physical design determines how the database is arranged and optimized on storage devices.

What are the components of a database management system?

Data definition language (DDL), data manipulation language (DML), and data dictionary.

What component is used in the physical design of a database design?

Data dictionary - DDA

What is application/data dependence?

Data is expected to be set up a specific way

What would be considered data redundancy?

Data is in multiple places

What is data integrity?

Data is not as tightly bound as it should be

What are relational databases based on?

Data normalization

What are common issues in traditional file environments?

Data redundancy (data is in multiple places), data inconsistency (in one place but not others), data isolation (access to data was only in one part of organization, should've been in more), data integrity (not bound as tightly as needed), security (no way to protect data) and application/data dependence (data expected to be set up on a specific way)

What is data inconsistency?

Data that is in one place but not others

What would be considered data isolation?

Data was only in part of the organization when it should've been in more parts

What are relational databases?

Databases that store data in form of related tables. This relational design is based on data normalization.

What is data manipulation language?

Deals with data, it determines how you extract data and use it. Known as SQL (structured query language)

What is an attribute?

Descriptive value associated with an entity. DATABASE FIELD IN A TABLE

What is one benefit of a relational database?

Ensures data integrity and referential intergrity

Which degree of databases is the worst?

First

How should we start questions when it comes to relationships between two entities?

From a singular perspective

What is an entity?

General category of data. Database table.

How do I determine what my relationships are?

Get the right questions to ask

Which database is most intuitive but doesn't have as much flexibility?

Hierarchical

How do you denote a many to many relationship?

N:M

What is an example of a one-to-one relationship?

One student is given one ID card. That ID card is given one student.

What are the different types of relationships?

One to one, one to many and many to many

What is an example of a relational database?

Oracle, Microsoft SQL Server

What can sometimes represent attributes?

Ovals

What is a one-to-many relationship?

Potentially more than one. ALWAYS START WITH A SINGLE PERSPECTIVE. Considered over a span of time, not specific time.

What is data security?

Protection of data

If a database must be simplified into a 1:1 relationship, how is it done?

Relationships merge into one table, making one entity an attribute of the other.

What is a relational database?

Represents data as two-dimensional tables called relations, relates data across tables based on common data elements.

What is data definition language?

Specifies content and structure of databases and defines each data element to help create tables and fields. CREATES DATA DICTIONARY STRUCTURE

How do we start to examine a one-to-many relationship?

Start with a single perspective

What is the data dictionary?

Stores definitions of data elements and data characteristics. 'Database to keep track of your database.' Keeps track of the structure of your database. DOES NOT STORE ANY DATA.

Which degree of databases is the best?

Third

What are characteristics of a network database?

Very inflexible, depicts data logically as many-to-many relationships, not flexible in getting data out

What relationship cannot be created in Microsoft Access?

Many-to-many

What is an example of a one to many relationship question?

Is it possible for a single customer to have multiple loans?

What is a hierarchical database?

It organizes data in a tree-like structure, supports one-to-many parent-child relationships.

Where are hierarchical databases prevalent?

Larger legacy systems

What is a one-to-one relationship?

Made up of only two halves, singular perspective is imperative and this is the relationship that everything in a representation should simplified to.