Chapter 6: Database Fundamentals

With SQL, you have the ability to do the following:

- Manipulate data - Define data - Adminster data

What advantages does SQL offer?

- Reduces training time - Makes applications portable - Reduces the amount of data being transferred - Increases: application speed

Which is not a step of the database design process? - Create tables - Create the relationships - Investigate - Add the data

-Create tables (yes) -Create the relationships (yes) - Investigate (yes) -Add the data (no) Add the data is not a step in the database design process

What is an entity relationship model?

A data model that represents how all tables interact and relate to each other in the database.

What is a database? Give an example of a database in current use that affects you.

A database is Data that has been organized and logically related into a file or set of files to allow access and use. it is also a collection of logically related information used in n application to create meaningful output for end users. Examples of databases that I am currently using: - Registration - Student Transcripts - Grocery store checkout - Grading - Library - Health insurance

What uniquely identifies a row in a table?

A row in a table (also called a record or tuple) is a collection of columns. Each row contains information that can be arranged into columns describing the row.

What is an index?

A special file that occupies its own space and specifies one or more columns that determine how information stored in a table can be accessed more efficiently.

Create a normalized database to support a space shuttle launching application. The government wants to keep track of astronauts, space shuttles, and launch history. Define the tables, primary and foreign keys, and indexes. Make sure the tables are finalized in the third normalized state.

Answers can vary

Draw and ER model to represent the space shuttle launching application designed in Question 11. Make sure you show the relationships, label the cardinality, and show primary and foreign keys.

Answers can vary

Using the fields listed, normalize the data by organizing the fields into tables. Hint: You should end up with at least three tables.

Answers can vary

Second normal form says:

Apply 2NF to tables with multiple keys to eliminate redundant data.

What is normalization, and what problems does it solve?

Normalization is the process of structuring tables to eliminate duplication and inconsistencies in data. The first problem is that if a database isn't normalized correctly, it can't represent certain real-world information items. The second problem occurs when the database contains redundancies (repetitions) in data, which simply wastes time and storage space. The third problem occurs when important information has been excluded during the design of the data structures.

Explain the first three normalization forms.

First normal form (1NF): Eliminate repeating fields or groups, of fields from the table, and confirm that every column has only one value by creating a new record in the table. Second normal form (2NF): First normal form has already been applied to the table, and every column that isn't part of the primary key is fully dependent on the primary key. Third normal form (3NF): Eliminate columns that aren't dependant on only the primary key.

What does NULL or NOT NULL mean in SQL?

NULL - Does Not Require Data NOT NULL - Requires Data

Which of the following SQL SELECT options is used to organize the data being returned? - ORDER BY - SORT ORDER - WHERE - None of the above.

ORDER BY is used to organize the data being returned

What are the three types of relationships discussed?

One-to-One (1:1), One-to-many (1:M), and many-to-many (M:M)

What are primary keys? What are foreign keys?

Primary keys are used to uniquely identify table entries; foreign keys are columns in one table that reference a primary key in another table.

The table is listed in what order (primary key)?

Record#

What is cardinality?

Show the numeric occurrences between entities in an ER model.

What specifies how the information in an entity is organized?

Sort Key

What are fourth and fifth normal forms?

They are rarely used but further break down tables into non-dependent, non-redundant forms.

The SQL INSERT INTO statement allows adding multiple records in one statement. True or False?

True. The INSERT INTO statement is used to add new rows of data to the table and follows the syntax: INSERT INTO table_name [(column1, column2,...)] VALUES (constant1, constant2,....)

What is functional dependency?

When a column's value is dependant on another column's value.

Structured Query Language (SQL) is a powerful database language for:

defining, maintaining, querying, and administrating data.

Write an SQL INSERT INTO statement to add the team Bears. The Bears have 3 wins and 9 losses.

insert into teams values (6, 'Bears', 3, 9); OR insert into teams (record_num, team, wins, losses) values (6, 'Bears', 3, 9);

Write a SQL insert INTO statement to add the team Lions. The Lions have 9 wins and 3 losses.

insert into teams values (7, 'Lion', 9, 3); OR insert into teams (record_num, team, wins, losses) values (7, 'Lions', 9, 3); The record number could be different, depending on whether question #5 was done.

When creating a table in SQL, you must specify whether the column is NULL or NOT NULL. True or False?

False. Because creating databases is nothing but creating tables.

Which of the following is not a Valid DBMS? - SQL Server - C++ - Oracle - DB2

C++ is not a valid DBMS

Review the Error_Status_Code column in the normalized tables The user wants to see the description displayed for the Error_Status_Code column. This description will also be used in a drop-down list box when the user is entering information for the error What can you, the database designer, do to enhance the tables' current design and make the database structure more flexible so that more Error_Status_Codes can be added to the system?

CREATE TABLE ErrorStatus (Error_Status_Code char(1) NOT NULL, Error_Status_Desc char(50) NOT NULL );

Review the Error_Priority_Code column in the normalized tables The user wants to see the description displayed for the Error_Priority_Code column. This description will also be used in a drop-down list box when the user is entering information for the error. What can you, as the database designer, do to enhance the table's current design and make the database structure more flexible so that more Error_Priority_Codes can be added to the system?

CREATE TABLE PriorityDesc (Error_Priority_Code number NOT NULL, Error_Priority_Desc char(50) NOT NULL );

What does DBMS stand for?

Database management System. - A program for managing storage, access, and modifications to a database.

What is the set of possible values for a column?

Domain

Third normal form says:

Eliminate columns that aren't dependant on only the primary key. 3NF is the same concept as 2NF, but it applies to tables having single primary keys.

First normal form says:

Eliminate repeated column values by making a new row in the table and copying the common datat to the column values.

By default, data returned by the SQL SELECT statement is in descending order. True or False?

FALSE, they are sorded in ascending order. The SELECT statement is the most commonly used SQL statement and is responsible for retrieving data from the tables in a database. The syntax for this statement is as follows: SELECT [DISTINCT] column_list FROM table_name [WHERE serach_condition] [ORDER BY order_list]

A column is divided into tables. True or False?

False

A table is divided into databases. True or False?

False

A composite key is a column containing unique information. True or False?

False, A composite key is a primary key made up of more than one column.

What is a determinant?

In a database, any column you can use to determine the value assigned to another column in the same row.

What order is database information kept in?

It is kept in sequential order, but you can view the order of records in different formats by creating indexes and assigning sort keys.

What is normalization?

It is the process of structuring tables to eliminate unwanted redundancies and dependancies.

List and explain the six steps for designing a database.

Step 1: Investigate and define Investigate and research the information you plan to model. Define the purpose of the database and how it will be used. Step 2: Make a master column list Create a list of all fields where you need to store information along with their properties. Step 3: Create the tables Logically group fields into tables. Step 4: Work on relationships Define the relationships showing how one table works with another. Step 5: Analyse the design Analyze the work completed by searching for design errors, redefining tables as needed, and correcting any normalization violations. Step 6: Reevaluate Reevaluate database performance and ensure that it meets all of your reporting and form needs.

Write a SQL CREATE statement to create each table. Make sure you identify which fields you think should or should not allow NULL values.

The NOT NULL statement can vary, as shown: CREATE TABLE ErrorDesc (Error_Code char(4) NOT NULL, Error_Code_Desc char(40) NOT NULL ); CREATE TABLE Errors (Error_Log_Date Date Not NOT NULL, Error_Log_Time Time NOT NULL, User_Code char(8) NOT NULL, Error_Code char(4) NOT NULL, Error_Log_Desc char(80), Error_Status_Code char(1), Error_Priority_Code number ); CREATE TABLE Users (User_Code char(8) NOT NULL, User_First char(15) NOT NULL, User_Last char(25) NOT NULL, User_Password char(10) NOT NULL );

Write a SQL INSERT INTO statement to add at least three records to each table you created.

The answer is an example of what you might use for the Users table. Be aware that the values will be different. insert into users values ('mrooney' , 'mickey' , 'rooney' , 'dance'); insert into users values ('bboop', 'betty', 'boop' 'cartoon'); insert into users values ('bbunny', 'bugs', 'bunny', 'rabbit');

What is a relationship?

The manner in which one table relates to another table.

Write a SQL SELECT statement to list the table's contents in order of wins.

select * from teams order by wins desc; The table name can vary because it's not provided the documentation. Letter case does not matter. You might be tempted to leave out the desc keyword. End user looking at results probably want to see the team with the most wins at the top.

Write an SQL SELECT statement to list the table's content in order of wins and team.

select * from teams order by wins desc, team; You might be tempted to put the desc keyword after team, but descending order applies only to the wins column, not the team column. you can also list each field instead of using the * wildcard.

Write a SQL select statement to list all the table's contents.

select * from teams; OR select record_num, team, wins, losses from teams

Using the normalized tables, write a SQL SELECT statement to show all errors (Error_Log_Date, Error_Log_Time, Error_Code, Error_Status_Code, Error_Priority_Code, Error_Code_Desc, and Error_Log_Desc) Sort the information by Error_Log_Date and Error_Log_Time columns.

select Error_Log_Date, Error_Log_Time, Errors. Error_Code, Error_Status_Code, Error_Priority_Code, Error_Code_Desc, Error_Log_Desc from Errors, ErrorDesc order by Error_Log_Date, Error_Log_Time; If you're using Access and specified the Error_code column, you might have seen an error message stating that this field could refer to more than one table listed in the FROM clause. If you have more than one field with the same name used in two different tables, it's correct to precede the field name with the table name, followed by a period and then the field name (for example, Errors, Error_Code).

Using the normalized tables, write a SQL SELECT statement to show all information on each error along with the first and last name of the user who created the error.

select Error_Log_Date, Error_Log_Time, Errors.Error_Code, Error_Status_Code, Error_Priority_Code, Error_Log_Desc, Errors. User_Code, User_Last, User_First from Errors, Users order by Error_Log_Date, Error_Log_Time; If you're using Access and specified the Error_Code and User_Code columns, you might have seen an error message stating that these fields could refer to more than one table listed in the FROM clause. If you have more than one field with the same name used in two different tables, it's correct to precede the field name with the table name, followed by a period and then the field name (for example, Errors, Error_Code, and Errors. User_Code).

Using the normalized tables, write a SQL SELECT statement to show all users (User_Code, User_First, and User_last) Sort the information by the User_Last and User_First Columns.

select User_Code, User_First, User_Last from Users order by User_Last, User_First;