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
