Module 1

Create Table Command

- The command that creates base tables and tells the system what attributes will be in them.

Foreign Key

A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables

What is a foreign key?

A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables

Boyce-Codd Normal Form (BCNF)

A special type of third normal form (3NF) in which every determinant is a candidate key. A table in BCNF must be in 3NF. See also determinant.

Surrogate Key

A system-assigned primary key, generally numeric and auto-incremented.

What is a primary key?

A value that identifies a unique row in a table

What role does the STUDENT ID column play in the Grade table?

A. Part of the composite primary key C. Foreign Key

Date Data Type

An attribute value used with the input element to create a calendar picker.

Null

An unknown value; cannot be evaluated or compared because they are unknown.

Fifth Normal Form

Any remaining anomalies have been removed

A table must always contain both columns and rows

False

one-to-one relationship

In databases, a relationship in which each record in Table A can have only one matching record in Table B, and vice versa.

many-to-many relationship

In databases, a relationship in which one record in Table A can relate to many matching records in Table B, and vice versa.

A column name must be unique within a table

True

The Grade Type Table does not allow values to be null in any column

True

Attributes

individual data elements

(PK)

primary key

Optionality Relationship Type

- the associations between database tables and the real world entity types they model - one shown by a line

Codd's 12 Rules

12 rules published by Edgar Codd in 1985 that concisely define an ideal relational database

If a table is a child table in three different one to many relationships, how many foreign key columns does it have?

3 or more The table has at least three foreign key columns, Some foreign keys may consist of multiple columns

What is a recursive relationship?

7. A recursive relationship is an optional relationship in which a column can refer to another column in the same table. Because the relationshop is optional, this foreign key column allows null.

Denormalization

A process by which a table is changed from a higher-level normal form to a lower-level normal form, usually to increase processing speed. Denormalization potentially yields data anomalies.

Second Normal Form

A relation in first normal form in which every nonkey attribute is fully functionally dependent on the primary key.

First Normal Form

A relation that has a primary key and in which there are no repeating groups.

Third Normal Form

A relation that is in second normal form and has no transitive dependencies.

How is data stored in a relational database?

A relational database stores data in tables, essentially a two-dimensional matrix consisting of columns and rows.

one-to-many relationship

A relationship between two tables in a database in which one record in the primary table can match many (zero, one, or many) records in the related table.

non identifying relationship

A relationship in which the primary key of the related entity does not contain a primary key component of the parent entity.

Crow's Foot notation

A representation of the entity relationship diagram that uses a three-pronged symbol to represent the "many" sides of the relationship.

Referential Integrity

A set of rules that Access uses to ensure that the data between related tables is valid.

Fourth Normal Form (4NF)

A table that is in 3NF and contains no multiple independent sets of multivalued dependencies.

Try to match each of the SQL commands on the left with a verb from the list on the right:

Create = define Update = manipulate Grant = control

What are the categories of SQL commands? Give and example of each of them.

Data Manipulation= Select, Insert Update, Delete, Merge; Data Definition=Create, Alter, Drop, Truncate, Rename; Data Control=Grant, Revoke; Transaction Control=Commit, Rollback, Savepoint

Why is it important to control access to data in a database?

Data can contain sensitive information to which some users should have limited access privileges. Some users may be allowed to query certain data but not change it, while others are allowed to add data to a database, but not delete it. By controlling access to data, the security of the data is assured for all users.

How is data organized in a relational database?

Data is organized by placing like pieces of information together in a table that consists of columns and rows.

NOT NULL

Ensures that a column will not have null values

A primary key may contain NULL values

False A primary key can never contain null values

The section table has no foreign key columns

False The section table has the Course No and the Instructor ID columns as foreign keys

An entity relationship diagram depicts entities, attributes, and tables.

False, The entity diagram is a logical model that doesn't deal with physical tables; rather, it deals with entities and attributes

The number of columns in a table matches the number of rows in that table.

False. The number of rows is independent of the number of columns in a table

The concept of denormalization deals with eliminating redundancy.

False. Actually, when you denormalize, you reintroduce redundancy

When you issue a SQL statement, you are concerned with the logical design of the database.

False. Logical Database design is a phase in the database development life cycle, and it ends with the physical database implementation. SQL works with the physical database implementation.

The logical model is derived from the schema diagram.

False. The schema diagram or physical model is derived from the logical data model.

What is the first normal form?

First normal form is a relation that has a primary key and in which there are no repeating groups.

Would it be possible to insert into the EMPLOYEE table as employee with a DEPT_NO of 10?

In the DEPT_NO column of the EMPLOYEE table you can ONLY enter values that exist in the DEPARTMENT table. The DEPARTMENT table is the parent table from which the child table, the EMPLOYEE table, gets its DEPT_NO values. Establishing a foreign key relationship highlights the benefit of referential integrity. Only valid primary key values from the parent table are allowed in the child's foreign key column, therefore avoiding orphan rows (child rows without parent rows). For example, you cannot enter a DEPT_NO of 10 in the EMPLOYEE table if such a value does not exist in the DEPARTMENT table.

intersection table

Placed at the junction of two tables to eliminate a many-to-many relationship

Rows

Represent one unique set of data within a table.

Logical Data Model

Shows the organization of data without indicating how it is stored, created, or manipulated

What is SQL?

Structured Query Language (SQL) is used to create and use databases, tables, and relationships. SQL is divided into two categories: SQL statements for database definition and SQL statements for database processing (querying and updating). The database definition commands are referred to as a data definition language (DDL), and the database query and update commands are referred to as a data manipulation language (DML). SQL was developed by IBM, and is endorsed as a national standard by the American National Standards Institute (ANSI). Although a newer standard, SQL3, exists, the most widely implemented version of SQL is the ANSI SQL-92 standard. SQL is not a full-featured programming language, but rather it is considered to be a data sublanguage.

ALTER TABLE

The SQL command used to make changes to table structure. When the command is followed by a keyword (ADD or MODIFY), it adds a column or changes column characteristics.

What are the essential phases of relational database development?

The essential phases of relational database development are Requirements Analysis, Conceptual Data Model, Logical Design, Physical Design, and Implementation.

Figure 1.5 displays a listing of an employee and its respective department table. Identify the columns you consider to be primary keys and foreign keys for the tables.

The primary key of the Employee table is the Employee_ID. The primary key of the Department table is Dept_No. The DEPT_NO is also the foreign key column of EMPLOYEE table and is common between the two tables.

normalization

The process of applying rules to a database design to ensure that information is divided into the appropriate tables. Also, eliminates redundancy.

Describe the nature of the relationship between the Order_Header table and the Order_detail table.

The relationship depicts a mandatory one-to-many relationship between the order header and the order detail table.

One of the tables in figure 1.25 is not fully normalized. Which normal form is violated?

The third normal form is violated on the order header table. The retail_price column belongs to the Product table instead.

How many table relationships are there in a relational database? What are they?

There are three table relationships in a relationship database. They include: One-to-one, one-to-many, and many-to-many.

VARCHAR2

This datatype is used to store variable length character strings. The string value's length will be stored on disk with the value itself. It's size depends on how many bytes you are actually going to store in the database. The number you specify is just the maximum number of bytes that can be stored (although 1 byte is minimum). You should use this datatype when you don't know the exact length of stored strings.

A nonidentifying relationship means that the foreign key is propagated as a nonkey attribute in the child entity or child table.

True

A null value means the the value is unknown

True

A table should contain at least one column? True or False

True

A university's listing of students and the classes they are enrolled in is an example of a database

True

In a mandatory relationship, null values are not allowed in the foreign key column.

True

It is best to avoid primary keys that are subject to updates? True or false?

True

Referential Integrity ensures that each value in a foreign key column of the child table links to a matching primary key value in the parent table.

True

Referential Integrity requires the relationship between the foreign key and primary key to maintain values from the same domain

True

Repeating groups are a violation of the first normal form.

True

A foreign key may be null

True An example of a foreign key that allows null values is the zip column on the instructor column

A table can contain 10 million rows

True Each individual database software may have limits constrained by the hardware and software. It is not uncommon to have tables exceeding 10 million rows

Orphan rows are not allowed in the relational model

True The prevention of orphan rows, thereby preserving the parent-child relationship between tables, is key to the success of a relational database design.

The crow's foot depicts the M of a 1:M relationship.

True. Another cardinality notation used to depict one-to-many relationships is 1:M or 1:N.

Grant in Command

Used to give access to objects within database

unique identifier

Way of uniquely identifying each record in the database

Figures 1.27 and 1.28 depict the logical and physical model of a fictional movie rental database. What differences exist between the entity relationship diagram and the physical schema diagram?

While some logical and physical models are identical, these figures exhibit distinguishing differences you may find in the retail world.

Is it possible to have a table with no rows at all?

Yes, it is possible, though clearly it is not very useful to have a table with no data.

How would figure 1.26 need to be changed if an employee does not have to belong to a department?

You change the relationship line on the department table end to make it optional. This has the effect that the Department_ID column on the Employee table can be null. That is, a value is not required.

How would figure 1.25 need to be changed to add information about the sales representative who took the order?

You need to add another table that contains the sales representative's name, Sales_Rep_ID and any other relevant information. SalesRep_ID then becomes a foreign key in the order header table

Physical Data Model

a graphical model of the physical design implementation of the database. What the programmers use to learn about the database and the relationship between the tables

relational database management system

allows users to create, read, update, and delete data in a relational database

Insert Command

allows you to add new rows to the table

Select Command

allows you to query data

Database

an organized collection of data

null-valued attribute

assigned to an attribute when no other value applies or when a value is unknown

composite primary key

consists of the primary key fields from the two intersecting relations

Relational databases offer___

data independence

Indexes

data structures that support efficient access to the data

Number Data Type

data type which is used when numeric values are entered to be used in calculations, except not for monetary amounts

Data warehouse applications

database applications that benefit users who need to analyze larg data sets from various angles

Value

each intersection of a column and a row in a table

Cardinality

expresses the specific number of instances in an entity

(FK)

foreign key

Requirement Analysis Phase

gathering data requirements that identify the needs and wants of the users. The output of this phase includes a list of individual data elements that need to be stored in a database.

char data type

holds any single character

Conceptual Data Model

logically groups the major data elements from the requirements analysis into individual entities.

Cardinality of Relationships

one to one, one to many, many to many

Columns

organize data further in a table. Each column represents a single low-level detail about a particular set of data you find in a column.

Database Management System (DBMS)

software that allows the creation, retrieval and manipulation of data

Identifying relationship

the primary key is propagated to the child entry as part of the primary key

Based on Figure 1.26, would the SSN column be a better primary key column than the employee id column?

the requirement for a primary key is that it is unique, not subject to updates, and not null

Implementation

transfer from the logical to the physical models.

Tables

typically contains data about a single subject. Each table has a unique name that signifies the contents of the data. A database typically contains many tables