All data in a relational database is structured in tables:
A table has a name, a fixed sequence of columns, and a varying set of rows.
A column has a name and a data type.
A row is an unnamed sequence of values. Each value corresponds to a column and belongs to the column’s data type.
A cell is a single column of a single row.
table must have at least one column but any number of rows
A table without rows is called an empty table
a table is also called a relation
Rules Governing Tables
Exactly one value per cell
A cell may not contain multiple values
Unknown data is represented with a special NULL value
No duplicate column names
Duplicate column names are allowed in different tables, but not in the same table
No duplicate rows
No two rows may have identical values in all columns
No row order
Rows are not ordered
organization of rows on a storage device never affects query results
called data independence
allows database administrators to improve query performance without affecting results
Database Table Statements
Creating Tables
The CREATE TABLE statement creates a new table by specifying the table name, column names, and column data types.
fails if the named table already exists
to avoid failure, use the optional clause IF NOT EXISTS
Example
CREATE TABLE Employees(id_num int IDENTITY(1,1),fname varchar (20),mi char(1),lname varchar(30));
creates a table called Employees with an ID field called “ID-NUM” containing a value that counts up and provides a unique number for each record entered
creates a first name, last name, and middle initial fields with varying lengths
When creating a table using a script, need to include:
at least one field
the field data type
Deleting Tables
The DROP TABLE statement deletes a table, along with all the table’s rows, from a database.
fails if the named table does not exist
to avoid failure, use the optional clause IF EXISTS