Chapter 1-3
Make certain that the column contains only a single value
A column can potentially store several instances of the same type of value is known as a Multivalued column
Multivalued column
A column that contains multiple phone numbers is an example of a multi value column
Deletion rule
A deletion rule dictates what happens when a user makes a request to delete a row and a primary table of a one to one relationship or in the table of a one side of a one to many relationship
many-to-many relationship
A pair of tables is in a many to many Relationship when I single row in the first table can be related to many rows in the second table, and a single row in the second table can be related to many rows in the first table. To establish relationship properly, you must create what is known as a linking table.
Views
A view is a virtual table composed of columns from one or more tables in the database. The tables that comprise the view are known as base tables.
Columns
Also known as fields. A column is the smallest structure in the database, and it represents a characteristic of the subject of the table to which it belongs. Columns are structures that store data.
Rows
Also known as records. A row represents a unique instance of the subject of a table. It is composed of the entire set of columns in a table, regardless of whether or not the columns contain any values.
mandatory participation
At least one row must exist in this table before you can enter any rose into the other table
Data Manipulation Language (DML)
DBMS language that changes database content, including data element creations, updates, insertions, and deletions
Make certain the column appears only once in the entire database
Exception to the rule is when you're using a column to establish a relationship between two tables
Is the name unique and descriptive enough to be meaningful to your entire organization?
Giving your table a unique name ensures that each table in the database represents a different subject that everyone in the organization will understand what the table represents
Relationships
If rose in a given table can be associated in someway with rose in another table, the tables are said to have a relationship between them.
Is the name descriptive and meaningful to your entire organization?
If users and several departments are going to work with the database, make sure you choose a name that is meaningful to everyone who has access to columns.
Did you use an acronym or abbreviation as a column name
If you did, change it
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.
keys
Keys are special columns that play very specific roles within a table the type of key determines its purpose within the table.
Is the column name clear and unambiguous?
Phone number is a column name that can be very misleading. What kind of phone number is the call I'm supposed to represent? A home phone? A work phone? A cell phone? Learn to be so specific.
Tables
Tables are the main structures in the database. Each table always represents a single specific subject. The logical order of rows and columns within a table is absolutely no importance.
Degrees of participation
The degree of participation for a given table is represented by two numbers that are separated with a comma and enclosed within parentheses the first number indicates the minimum possible number of related rows in the second number indicates the maximum possible number of related rows.
Make sure the column represents a specific characteristic of the subject of the table.
The idea here is to determine whether the column truly belongs in the table if it isn't germane to the table, remove it or perhaps move it to another table.
Data Definition Language (DDL)
The language that allows a database administrator to define the database structure, schema, and subschema.
Restrict deletion rule
The restrict deletion rule does not allow you to Delete the requested row when there are related rose in the subordinate table of a one to one relationship or in the table on the mini side of a one to many relationship
optional participation
There is no requirement for any rose to exist in this table before you enter any rose in the other table
Did you use a name that implicitly or explicitly identifies more than one characteristic?
These types of names are easy to spot because they typically use the words and or or.\- -
The type of Participation assigned to a given table determines whether a row must exist in that table before you can enter a row into the other table
Two types of participation are mandatory and optional
one-to-many relationship
When a pair of tables has a one to many relationship, a single row in the first table can be related to many rows in the second table, but a single row and the second table can be related to only one row in the first table.
Cascade deletion rule
When the cascade dilation role is in force, deleting the row on the one side of a relationship causes the system to automatically delete any related rose in the subordinate table of a one to one relationship or in the table of the mini side of a one to many relationship
many-to-many relationship
You establish a mini to mini relationship by creating a linking table. Defined the linking table by taking a copy of the primary key of each table in the relationship and using them to form a structure of a new table. Together they form the composite primary key of a linking data table separately they serve as a foreign key
one-too-many relationship
You establish a one to many relationship by taking the primary key of the table on the one side and inserting it into the table on the mini side, where it becomes a foreign key
One to one relationship
You establish a one to one relationship by taking the primary key from the primary table and inserting it into the subordinate table where becomes a foreign key