Table of content
In this blog we will cover below topics:
SQL as Data Definition Language
Creating Relational Databases and Tables using SQL
Primary Key of a Table
Primary Keys, Super Keys, and Candidate Keys
Foreign Key Constraints and Referential Integrity
Other Types of Constraints
Insertion/Modifying/Deleting Data Using Data
Using PostgreSQL/Demo
SQL :: Creating a Table
Creating a Relational Database (RDB) using SQL
Primary Keys
Recall that a Table (i.e., a Relation) is just a set of Rows (i.e., Tuples), and a set cannot have duplicate Rows (Tuples).
It is the smallest set of attributes (Columns and Data Types) that make a Tuple (Row) unique
* In the CREATE TABLE statement on the previous slide we specify that no two Tuples of the instructor table can have the same value on ID attribute. Tuples(Rows) can have the same values for every other column except for the ID attribute
Keys: Primary Keys, Super Keys, and Candidate Keys
“Primary Key is a set of Columns which uniquely identify the Tuples (Rows) in a Relation (Table). It cannot have Null values.“ “Primary Keys are Candidate Keys selected by the database administrator to uniquely identify Rows in a Table.”
“Super Key is a combination of columns that uniquely identifies any row within a relational database management system (RDBMS) table. They can have Null values”
“Candidate Key is a set of Columns which uniquely identify the Tuples (Rows) in a Relation (Table) but can have Null values.” “Candidate Key is a single key or a group of multiple keys that uniquely identify rows in a table.”
SQL Keys can either be one more columns
Super Key are combinations of columns that can uniquely identify Rows in a Table.
* Can have redundant Columns that might not be important for identifying tuples.
Candidate Keys are a subset of Super keys, but not vice-versa
* They contain only those attributes which are required to uniquely identify tuples.
* All Candidate keys are Super keys.
Primary Key is a Candidate key chosen to uniquely identify tuples in the table.
* Primary key are unique columns that are Not Null
* There can be multiple Super keys and Candidate keys in a table, but there is one Primary key per table.
* This is explicitly designated by a DBA
What is a Foreign Key?
“A Foreign Key is/are column(s) in a Relational Database Table that provides a link between data in Two Tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.”
Create Table with Foreign Key
Foreign Key Constraints (FKC) and Referential Integrity(RI)
FKC says “that the Foreign Key(s) used in a table can only contain values that are in the referenced Primary Key Column for the table where the Primary Key exists”
A Table (e.g., instructor) has column(s) (e.g., dept_name) that always has values that are checked to the Primary Key column(s) in another table (e.g., department)
* The Table with the Primary Key is called the Validator table
* Foreign Key Reference from the table with the Foreign Key to the Validator table.
A Database in which all Foreign Key Constraints are respected is said to have Referential Integrity.
Using SQL code for creating a table we can specify what action to take if insertion of data violates a foreign key constraint on table: Block the insertion? Take a corrective action on the validator table? No action?
* This will be covered in the elective course on SQL.
Create Table with Not Null constraint
Null Values
Some of the attributes in some of the Rows in a Table can have Null Values.
Null denotes an unknown value or a value that doesn’t exist.
Arithmetic expressions with a Null value results in a Null value
Aggregate functions (e.g., avg, mean, count) simply ignore Null values
Other Constraints
Check (limit the Data that is inserted into a column) (e.g., want ages between 18 and 35)
Assertions (checks if conditions for a Database or Database Table are satisfied before change is made) (e.g., Do not allow varchar to be inserted into an int column )
Triggers (Automatically Executed Stored Procedures after changes to data occurs) (e.g., Before Updating a Row of Data, Save the current Row to a file)
Not all RDBMS support these constraints. The good ones do.
These will be covered in depth in an SQL elective course
Views
A View is a stored SQL query that is named and stored
* Equivalent to a Procedure in a Programming Language -> Python/R:
def doSomething(p1, p2): or doSomething <- function(p1,p2){}
Like Programming Language Procedures, Views can be called by name and used to create other Views and queries.
Views are useful for exposing portions of data stored in tables to users who should not be given access to all the data stored in those tables.
* For example, some employees may be permitted access to some part of the i instructor table without access to the salary information stored in that table. In this sense a View is like role-based access.
Why are Views Helpful for RDBs and Tables?
Hide Complex Queries and Data Types from Users
* Large SQL Joins and Queries of many Tables can be discussed by just
calling a View keyword
Combines Data (e.g., functions like count(), avg()) and shows the computed results as metadata -> count(some_column) is a View
Takes little Resources: the database has the SQL code for the View but not the actual data derived from the code
Provide Data and User Security: in big companies, Users get access to Database Views with indirect access to large scale databases. A View gives users read and aggregate function access to the Tables and Data for reporting or analytics
Views: Example #1
General format:
create view as view_name + Any SQL queryTo create a view on instructor table without salary information
create view as no_salary_instructor
select id, name, dept_name
from instructorViews: Example #2
Let’s create a View that gives Faculty Information of which Faculty advises which students:
We will not share Confidential Information like ID and Salary
We will use the previously created View called: no_salary_instructor
We will call our new View: faculty_student_advising
create view as faculty_student_advising
select N.name AS InstructorName, N.dept_name AS InstructorDept, S.name AS StudentName, S.dept_name AS StudentDept
from student as S, advisor as A, no_salary_instructor as N
where S. ID = A.s_id AND N.id = A.i_idNote: Need to create aliases on ‘name’ and ‘dept-name’ to disambiguate between student info and instructor info.
Views: Example #3
Queries can also be created using views. Before execution of the query, it is expanded to include the definition of the view.
Let’s Create a View to Find the building in which any instructor with the name ‘Kitty’ is has an office in it:
create view as Kitty
select D.building
from department AS D, no_salary_instructor AS N
where D.dept_name = N.dept_name AND N.name LIKE “%Kitty%”Functional Dependency: Definition
Functional Dependency (FD) is “a constraint in which one value of an attribute type determines the value of another.”
Functional Dependency: “X -> Y between two sets of attribute types X and Y implies that X (independent set) determines Y (dependent set).”
FD Example: “Every Employee must have a Social Security Number Implies: SSN -> ENAME”
Functional Dependencies (FD): In Practice
FD are used to encode the Business Rules of the Organization for which the RDB has been created.
* Ex: All employees of a certain rank are assigned to designated parking lot.
* Ex: Each department is assigned to a building and a budget
FD can also be used to provide additional constraints for attributes in tables.
* Ex: Any Student with Junior or higher (i.e., Senior) standing
must have completed at least 48 Credit Hours
FDs are also used to redesign RDBs
Functional Dependencies (FDs): In Practice (Continued)
An organization’s business rule “Every department must be assigned to a building and a budget” must be encoded in the DB for the organization and every valid instance of the DB must satisfy this business rule.
* This means no valid instance of the DB can contain two Rows with
same department name and two different buildings (or budgets)
FDs are additional constraints on the database
But an RDBMS does not directly enforce FDs
* FDs are enforced by a good design (or redesign) of a Database
* This means that the attributes involved in a Functional Dependency
are made to occur in a single table and constraints such as Primary Key or uniqueness constraints are used to enforce the Functional Dependency.
Functional Dependencies: Example
FDs and Schema: Example
Suppose that in our university schema had one table instructor-dept with the following schema:
instructor-dept (id, name, dept_name, salary, building, budget)
What is wrong with this Table?? How will you fix it??
Break the table up into two tables: instructor and department
Problems Caused by FDs
Redundancy anomaly
* Building and budget info is
needlessly repeated
* Waste of DB space
Insertion Anomaly
* If the philosophy budget is increased,
it could be updated in one row and not the other rows.
Deletion Anomaly
* If all the philosophy faculty are deleted from the DB, then there is no record
of the location or budget of the philosophy department since there is no separate department table
Decomposition
Decomposition is breaking a schema/table down into multiple Schemas/Tables – We just did that!!!
If We Decompose instructor-dept into two schemas (as in our original DB design), instructor(id, name, dept_name) and department (dept_name, building, budget), all the redundancy problems go away.
* The information about which department is in which building is now
independent of the content of the instructor table
* So this information is not lost even if instructor table has no row for any
members of some department.
The FD can be enforced by the DBMS by making dept_name the primary key in departments and making dept_name in instructor a foreign key reference to department
Problems with Decomposition
Decomposition needs to be done carefully otherwise it can generate its own problems
* No Information that is in the DB before decomposition should be lost as a
result of decomposition
* No New Information that is NOT in the DB before decomposition should
be generated as a result of decomposition
* When this condition is satisfied it is called a ‘Lossless Join Decomposition’
* We check whether the Decomposition is a lossless join decomposition
by doing a join of the decomposed schemas and checking if there are any new tuples that were not in the original table.
Decomposition: Examples
We are also providing complete database tutorials if you need any database project help, database query help, database assignment help, SQL homework Help then send requirement details at:
And get instant help with an affordable price.
In this case we say that the functional