Week 6 - More SQL & Rest - Referential Integrity with SQL, Intro to REST Flashcards
SQL data constraints are used to restrict the values of data which are inserted into a table. T/F?
True
Constraints help define the integrity constraints of the database schema. Common constraints include
Primary key
Foreign Key
Not Null
Unique
Default
Check
Create Index
What is a foreign key?
is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.
Syntax: Create a table with a foreign key using alter
CREATE TABLE Branch(
branch_id INT PRIMARY KEY,
branch_name VARCHAR(20)
);
ALTER TABLE employee ADD branch_id INT;
ALTER TABLE employee ADD FOREIGN KEY (branch_id) REFERENCES Branch(branch_id);
Define referential integrity?
is the relationship between tables. Each table in a database has a primary key, this primary key can appear in other tables because of its relationship to data within other tables. When a primary key from one table appears in another table, it is called a foreign key.
Syntax: table with foreign key
CREATE TABLE InternationalStudent(
country_id INT PRIMARY KEY,
name VARCHAR(20),
student_id INT,
FOREIGN KEY(student_id) REFERENCES Student(student_id)
ON DELETE CASCADE
);
Describe the use of an alias in a database
Aliases are used to give a temporary name to a table or a column in a table with the intention to support a specific query.
Advantages of Alias
- It provides a very useful feature that allows us to achieve complex tasks quickly.
- It makes the column or table name more readable.
- It allows us to combine two or more columns
- It makes the table more user-friendly.
Syntax to create alias for column
SELECT column_name AS alias_name FROM table_name
Syntax to create alias for table
SELECT column_name1, column_name2 FROM table_name AS alias_name
To define and implement MULTIPLICITY
Multiplicity can be clasified as four types:
one to one relationship
one to many relationship
many to one relationship
many to many relationship
Multiple instances of the same or multiple entities can be associated with one or many instances.
The Multiplicity is a constraint on the cardinality, which shall:
not be less than the lower bound
and not greater than the upper bound specified
define Normalization
Normalization is the process of efficiently organizing data in a database. The two main objectives of normalization are, eliminate redundant data ie to make sure that the same data is not stored twice, and to ensure data dependencies make sense, ie to store only relational data in the table. Both of these are important because they reduce the amount of space in a database and ensure that data is logically stored.
First Normal Form:
A relation is in 1NF if all its attributes have an atomic value.
The first normal form (1NF) is conclusive of a relational database. If we are to consider a database relational, then all relations in the database are in 1NF.
Second Normal Form:
A relation is in 2NF if it is in 1NF and all non-key attributes are fully functionaly dependent on the candidate key
Second normal form (2NF) deals with the elimination of circular dependencies from a relation. We say a relation is in 2NF if it is in 1NF and if every non-key attribute is completely dependent only on the Primary Key
A non-key attribute is any column that can not be used to uniquely identify the table.
Third Normal Form:
A relation is in 3NF if it is in 2NF and there is no transitive dependency.
Third Normal Form: Third normal form (3NF) deals with the elimination of non-key attributes that do not describe the Primary Key.For a relation to be in 3NF, the relationship between any two non key attributes, or groups of non-key attributes, must not be in a one to one relation.
The attributes should be mutually independent which means, none of the attributes should be functionally dependent on any combination of attributes. This mutual independence makes sure that any update on the individual attribute will not affect other attributes in a row.