BCA DBMS With Oracle Semster-3

DBMS with Oracle

Introduction to database system:

Definition:

Database refers to a collection of electronic records that could be processed to produce useful information. The data can be accessed, modified, managed, controlled and organized to perform various data-processing operations. The data is typically indexed across rows, columns and tables that make workload processing and data querying efficient. There are different types of databases: Object-oriented, Relational, Distributed, Hierarchical, Network and others. In enterprise applications, databases involve mission-critical, security-sensitive and compliance-focused record items that have complicated logical relationships with other datasets and grow exponentially over time as the userbase increases. As a result, these organizations require technology solutions to maintain, secure, manage and process the data stored in databases. This is where Database Management System come into play.

Limitations of traditional file system:

1. Separated and Isolated Data: To make a decision, a user might need data from two separate files. First, the files were evaluated by analysts and programmers to determine the specific data required from each file and the relationships between the data and then applications could be written in a programming language to process and extract the needed data. Imagine the work involved if data from several files was needed.

2. Duplication of data: Often the same information is stored in more than one file. Uncontrolled duplication of data is not required for several reasons, such as:

• Duplication is wasteful. It costs time and money to enter the data more than once

• It takes up additional storage space, again with associated costs.

Advantages of DBMS:

A Database Management System (DBMS) is defined as the software system that allows users to define, create, maintain and control access to the database. DBMS makes it possible for end users to create, read, update and delete data in database. It is a layer between programs and data.

Compared to the File Based Data Management System, Database Management System has many advantages. Some of these advantages are given below:

Reducing Data Redundancy

The file based data management systems contained multiple files that were stored in many different locations in a system or even across multiple systems. Because of this, there were sometimes multiple copies of the same file which lead to data redundancy. 

This is prevented in a database as there is a single database and any change in it is reflected immediately. Because of this, there is no chance of encountering duplicate data.

Sharing of Data

In a database, the users of the database can share the data among themselves. There are various levels of authorisation to access the data, and consequently the data can only be shared based on the correct authorisation protocols being followed. 

Many remote users can also access the database simultaneously and share the data between themselves.

Data Integrity

Data integrity means that the data is accurate and consistent in the database. Data Integrity is very important as there are multiple databases in a DBMS. All of these databases contain data that is visible to multiple users. So it is necessary to ensure that the data is correct and consistent in all the databases and for all the users. 

Data Security

Data Security is vital concept in a database. Only authorised users should be allowed to access the database and their identity should be authenticated using a username and password. Unauthorised users should not be allowed to access the database under any circumstances as it violates the integrity constraints.

Privacy

The privacy rule in a database means only the authorized users can access a database according to its privacy constraints. There are levels of database access and a user can only view the data he is allowed to. For example – In social networking sites, access constraints are different for different accounts a user may want to access.

Backup and Recovery

Database Management System automatically takes care of backup and recovery. The users don’t need to backup data periodically because this is taken care of by the DBMS. Moreover, it also restores the database after a crash or system failure to its previous condition. 

Data Consistency

Data consistency is ensured in a database because there is no data redundancy. All data appears consistently across the database and the data is same for all the users viewing the database. Moreover, any changes made to the database are immediately reflected to all the users and there is no data inconsistency.

Components of DBMS:

Software Icon

Software
This is the set of programs used to control and manage the overall database. This includes the DBMS software itself, the Operating System, the network software being used to share the data among users, and the application programs used to access data in the DBMS.

Hardware Icon

Hardware
Consists of a set of physical electronic devices such as computers, I/O devices, storage devices, etc., this provides the interface between computers and the real world systems.

Data Icon

Data
DBMS exists to collect, store, process and access data, the most important component. The database contains both the actual or operational data and the metadata.

Procedures Icon

Procedures
These are the instructions and rules that assist on how to use the DBMS, and in designing and running the database, using documented procedures, to guide the users that operate and manage it.

Access Language

Database Access Language
This is used to access the data to and from the database, to enter new data, update existing data, or retrieve required data from databases. The user writes a set of appropriate commands in a database access language, submits these to the DBMS, which then processes the data and generates and displays a set of results into a user readable form.

Query Processor Icon

Query Processor
This transforms the user queries into a series of low level instructions. This reads the online user’s query and translates it into an efficient series of operations in a form capable of being sent to the run time data manager for execution.

Run Time

Run Time Database Manager
Sometimes referred to as the database control system, this is the central software component of the DBMS that interfaces with user-submitted application programs and queries, and handles database access at run time. Its function is to convert operations in user’s queries. It provides control to maintain the consistency, integrity and security of the data.

Data Manager Icon

Data Manager
Also called the cache manger, this is responsible for handling of data in the database, providing a recovery to the system that allows it to recover the data after a failure.

Database Engine Icon

Database Engine
The core service for storing, processing, and securing data, this provides controlled access and rapid transaction processing to address the requirements of the most demanding data consuming applications. It is often used to create relational databases for online transaction processing or online analytical processing data.

Data Dictionary Icon

Data Dictionary
This is a reserved space within a database used to store information about the database itself. A data dictionary is a set of read-only table and views, containing the different information about the data used in the enterprise to ensure that database representation of the data follow one standard as defined in the dictionary.

Report Writer Icon

Report Writer
Also referred to as the report generator, it is a program that extracts information from one or more files and presents the information in a specified format. Most report writers allow the user to select records that meet certain conditions and to display selected fields in rows and columns, or also format the data into different charts.

Database Architecture:

Database architecture uses programming languages to design a particular type of software for businesses or organizations.Database architecture focuses on the design, development, implementation and maintenance of computer programs that store and organize information for businesses, agencies and institutions. A database architect develops and implements software to meet the needs of users.

The design of a DBMS depends on its architecture. It can be centralized or decentralized or hierarchical. The architecture of a DBMS can be seen as either single tier or multi-tier. The tiers are classified as follows :

  1. 1-tier architecture
  2. 2-tier architecture
  3. 3-tier architecture
  4. n-tier architecture

1-tier architecture:

One-tier architecture involves putting all of the required components for a software application or technology on a single server or platform.

2-tier architecture:

The two-tier is based on Client Server architecture. The two-tier architecture is like client server application. The direct communication takes place between client and server. There is no intermediate between client and server.

2-tier architecture

3-tier architecture:

A 3-tier architecture separates its tiers from each other based on the complexity of the users and how they use the data present in the database. It is the most widely used architecture to design a DBMS.

3-tier architecture

This architecture has different usages with different applications. It can be used in web applications and distributed applications. The strength in particular is when using this architecture over distributed systems.

  • Database (Data) Tier − At this tier, the database resides along with its query processing languages. We also have the relations that define the data and their constraints at this level.
  • Application (Middle) Tier − At this tier reside the application server and the programs that access the database. For a user, this application tier presents an abstracted view of the database. End-users are unaware of any existence of the database beyond the application. At the other end, the database tier is not aware of any other user beyond the application tier. Hence, the application layer sits in the middle and acts as a mediator between the end-user and the database.
  • User (Presentation) Tier − End-users operate on this tier and they know nothing about any existence of the database beyond this layer. At this layer, multiple views of the database can be provided by the application. All views are generated by applications that reside in the application tier.

n-tier architecture:

N-tier architecture would involve dividing an application into three different tiers. These would be the

  1. logic tier,
  2. the presentation tier, and
  3. the data tier.
n-tier architecture

It is the physical separation of the different parts of the application as opposed to the usually conceptual or logical separation of the elements in the model-view-controller (MVC) framework. Another difference from the MVC framework is that n-tier layers are connected linearly, meaning all communication must go through the middle layer, which is the logic tier. In MVC, there is no actual middle layer because the interaction is triangular; the control layer has access to both the view and model layers and the model also accesses the view; the controller also creates a model based on the requirements and pushes this to the view. However, they are not mutually exclusive, as the MVC framework can be used in conjunction with the n-tier architecture, with the n-tier being the overall architecture used and MVC used as the framework for the presentation .

Database Users:

1.Application Programmers

As its name shows, application programmers are the one who writes application programs that uses the database. These application programs are written in programming languages like COBOL or PL (Programming Language 1), Java and fourth generation language. These programs meet the user requirement and made according to user requirements. Retrieving information, creating new information and changing existing information is done by these application programs.

2.End Users

End users are those who access the database from the terminal end. They use the developed applications and they don’t have any knowledge about the design and working of database. These are the second class of users and their main motto is just to get their task done. There are basically two types of end users that are discussed below.

  • Casual User

These users have great knowledge of query language. Casual users access data by entering different queries from the terminal end. They do not write programs but they can interact with the system by writing queries.

  • Naive

Any user who does not have any knowledge about database can be in this category. There task is to just use the developed application and get the desired results. For example: Clerical staff in any bank is a naïve user. They don’t have any dbms knowledge but they still use the database and perform their given task.

3.DBA (Database Administrator)

DBA can be a single person or it can be a group of person. Database Administrator is responsible for everything that is related to database. He makes the policies, strategies and provides technical supports.

4.System Analyst

System analyst is responsible for the design, structure and properties of database. All the requirements of the end users are handled by system analyst. Feasibility, economic and technical aspects of DBMS is the main concern of system analyst.

Schemas and instances:

Database Schema

A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data.

A database schema defines its entities and the relationship among them. It contains a descriptive detail of the database, which can be depicted by means of schema diagrams. It’s the database designers who design the schema to help programmers understand the database and make it useful.

A database schema can be divided broadly into two categories −

  • Physical Database Schema − This schema pertains to the actual storage of data and its form of storage like files, indices, etc. It defines how the data will be stored in a secondary storage.
  • Logical Database Schema − This schema defines all the logical constraints that need to be applied on the data stored. It defines tables, views, and integrity constraints.

Database Instance

It is important that we distinguish these two terms individually. Database schema is the skeleton of database. It is designed when the database doesn’t exist at all. Once the database is operational, it is very difficult to make any changes to it. A database schema does not contain any data or information.

A database instance is a state of operational database with data at any given time. It contains a snapshot of the database. Database instances tend to change with time. A DBMS ensures that its every instance (state) is in a valid state, by diligently following all the validations, constraints, and conditions that the database designers have imposed.

Database languages (DDL,DML, DCL):

DDL (Data Definition Language) :

Data Definition Language is used to define the database structure or schema. DDL is also used to specify additional properties of the data. The storage structure and access methods used by the database system by a set of statements in a special type of DDL called a data storage and definition language. These statements define the implementation details of the database schema, which are usually hidden from the users. The data values stored in the database must satisfy certain consistency constraints.

DML (Data Manipulation Language) :

DML statements are used for managing data with in schema objects.
DML are of two types –

  1. Procedural DMLs : require a user to specify what data are needed and how to get those data.
  2. Declerative DMLs (also referred as Non-procedural DMLs) : require a user to specify what data are needed without specifying how to get those data. Declarative DMLs are usually easier to learn and use than procedural DMLs. However, since a user does not have to specify how to get the data, the database system has to figure out an efficient means of accessing data.

DCL (Data Control Language) :

A Data Control Language is a syntax similar to a computer programming language used to control access to data stored in a database (Authorization). In particular, it is a component of Structured Query Language (SQL).

Examples of DCL commands :

GRANT: allow specified users to perform specified tasks.
REVOKE: cancel previously granted or denied permissions.

The operations for which privileges may be granted to or revoked from a user or role apply to both the Data definition language (DDL) and the Data manipulation language (DML), and may include CONNECT, SELECT, INSERT, UPDATE, DELETE, EXECUTE and USAGE.

Unit-2

Conceptual Design:

Overview of DB design:

  • Conceptual design: (ER Model is used at this stage.)
    • What are the entities and relationships in the enterprise?
    • What information about these entities and relationships should we store in the database?
    • What are the integrity constraints or business rules that hold?
    • A database `schema in the ER Model can be represented pictorially (ER diagrams).
    • Can map an ER diagram into a relational schema.
  • Schema Refinement (Normalization): Check relational schema for redundancies and related anomalies.
  • Physical Database Design and Tuning: Consider typical workloads and further refine the database design.

E-R model:

An Entity–relationship model (ER model) describes the structure of a database with the help of a diagram, which is known as Entity Relationship Diagram (ER Diagram). An ER model is a design or blueprint of a database that can later be implemented as a database. The main components of E-R model are: entity set and relationship set.

What is an Entity Relationship Diagram (ER Diagram)?

An ER diagram shows the relationship among entity sets. An entity set is a group of similar entities and these entities can have attributes. In terms of DBMS, an entity is a table or attribute of a table in database, so by showing relationship among tables and their attributes, ER diagram shows the complete logical structure of a database. Lets have a look at a simple ER diagram to understand this concept.

A simple ER Diagram:

E-R Diagram

Attributes and its types:

Type of Attributes in DBMS

Single valued Attributes : An attribute, that has a single value for a particular entity. For example, age of a employee entity.

Multi valued Attributes : An attributes that may have multiple values for the same entity. For example colors of a car entity.

Compound /Composite Attribute : Attribute can be subdivided into two or more other Attribute. For Example, Name can be divided into First name, Middle name and Last name.

Simple/Atomic Attributes : The attributes which cannot be divided into smaller subparts are called simple or atomic attributes. For example, age of employee entity

Stored Attribute : An attribute, which cannot be derived from other attribute, is known as stored attribute. For example, BirthDate of employee.

Derived Attribute : Attributes derived from other stored attribute. For example age from Date of Birth and Today’s date.

Complex Attributes : If an attribute of an entity, is built using composite and multivalued attributes, then these attributes are called complex attributes. For example, a person can have more than one residence and each residence can have multiple phones, an addressphone for a person entity can be specified as – {Addressphone (phone {(Area Code, Phone Number)}, Address(Sector Address (Sector Number,House Number), City, State, Pin))}

Here {} are used to enclose multivalued attributes and () are used to enclose composite attributes with comma separating individual attributes.

Key Attribute : represents primary key. (main characteristics of an entity). It is an attribute, that has distinct value for each entity/element in an entity set. For example, Roll number in a Student Entity Type.

Non Key Attributes : These are attributes other than candidate key attributes in a table. For example Firstname is a non key attribute as it does not represent the main characteristics of the entity.

Required Attribute : A required attribute is an attribute that must have a data value. These attributes are required because they describe what is important in the entity. For example, In a STUDENT entity, firstname and lastname is a required attribute.

Optional Attribute/Null Value Attribute – An optional attribute may not have a value in it and can be left blank. For example, In a STUDENT entity, Middlename or email address is an optional attribute. as some students may not have middlename or email address.

Relationship and relationship sets:

Relationship Type:

Relationship Type can be defined the association between two entities.

In a school database, a student enroll a course. Here enroll is a relationship.

Relationship Set :

Relationship Set can be defined a set of similar type relationship. It is represented by a diamond and connecting the entities by line.

In a school database, There is a relationship between student set and course set.

Cardinality:

The term cardinality refers to the number of cardinal (basic) members in a set. Cardinality can be finite (a non-negative integer) or infinite. For example, the cardinality of the set of people in the United States is approximately 270,000,000; the cardinality of the set of integers is denumerably infinite.

In tables, the number of rows (or tuples) is called the cardinality. In practice, tables always have positive-integer cardinality. The reason for this is simple: tables with no rows, or with a negative number of rows, cannot exist. In theory, however, tables with denumerably infinite cardinality can exist. An example is a multiplication table of non-negative integers in which entries are implied for all possible values:

0123..
1123..
2246..
3369..
::::

The concept of cardinality is of interest to set theoreticians because it has been used to demonstrate that some infinite sets are larger than others. The cardinality of the set of real numbers is greater than the cardinality of the set of integers, even though both sets are infinite. The cardinality of the set of integers is called aleph-null or aleph-nought; the cardinality of the set of real numbers is called aleph-one.

One of the great mysteries of mathematics is contained in the question, “What is the cardinality of the set of points on a geometric line?” Generally it is presumed to be aleph-one; the set of points on a line is thought to correspond one-to-one with the set of real numbers. This is by no means a trivial supposition, and has become known as the Continuum Hypothesis.

Degree:

Degree of DBMS is the number of entities sharing one relationship which we refer as Degree of relationship . We mainly have three

Unary Relationship :

                          When entity has a relationship with itself , that’s a unary relationship.  Self-Joins are the perfect example.  Usually when entity has a parent-child relationship in its columns , then this happens.

unary relationship.jpg

Unary Relation Example could be that you need to pass certain subjects to take others as they are pre-requisites of them . Presentation will be :

      Binary Relationship :

When two entities share a relationship in database management system with each other that is called a binary relationship . Example could be Teacher teaches a Subject. Two entities one relationship.

binaryrelationship
      Ternary Relationship :

When three entities share a relationship with each other . Example could be a Students taking Subjects from Teachers.

ternary

Using the diagram of our  ERD Terminologies . Here Student , Teacher and Subject has a ternary relationship.  

You can see a design DBMS in making and we have created relationships among entities using relationships .

Generalization:

  • Generalization is like a bottom-up approach in which two or more entities of lower level combine to form a higher level entity if they have some attributes in common.
  • In generalization, an entity of a higher level can also combine with the entities of the lower level to form a further higher level entity.
  • Generalization is more like subclass and superclass system, but the only difference is the approach. Generalization uses the bottom-up approach.
  • In generalization, entities are combined to form a more generalized entity, i.e., subclasses are combined to make a superclass.

For example, Faculty and Student entities can be generalized and create a higher level entity Person.

DBMS Generalization

Specialization:

  • Specialization is a top-down approach, and it is opposite to Generalization. In specialization, one higher level entity can be broken down into two lower level entities.
  • Specialization is used to identify the subset of an entity set that shares some distinguishing characteristics.
  • Normally, the superclass is defined first, the subclass and its related attributes are defined next, and relationship set are then added.

For example: In an Employee management system, EMPLOYEE entity can be specialized as TESTER or DEVELOPER based on what role they play in the company.

DBMS Specialization

Aggregation:

In aggregation, the relation between two entities is treated as a single entity. In aggregation, relationship with its corresponding entities is aggregated into a higher level entity.

For example: Center entity offers the Course entity act as a single entity in the relationship which is in a relationship with another entity visitor. In the real world, if a visitor visits a coaching center then he will never enquiry about the Course only or just about the Center instead he will ask the enquiry about both.

DBMS Aggregation

Relational Model

Relational Model was proposed by E.F. Codd to model data in the form of relations or tables. After designing the conceptual model of Database using ER diagram, we need to convert the conceptual model in the relational model which can be implemented using any RDMBS languages like Oracle SQL, MySQL etc. So we will see what Relational Model is.

What is Relational Model?

Relational Model represents how data is stored in Relational Databases.  A relational database stores data in the form of relations (tables). Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE and AGE shown in Table 1.

STUDENT

ROLL_NONAMEADDRESSPHONEAGE
1RAMDELHI945512345118
2RAMESHGURGAON965243154318
3SUJITROHTAK915625313120
4SURESHDELHI18

Relation

Relation is sometimes used to refer to a table in a relational database but is more commonly used to describe the relationships that can be created between those tables in a relational database.

In relational databases, a relationship exists between two tables when one of them has a foreign key that references the primary key of the other table. This single fact allows relational databases to split and store data in different tables, yet still link the disparate data items together. It is one of the features that makes relational databases such powerful and efficient stores of information.

Domain

Each domain contains a set of data stored in Guardium that relates to a specific purpose or function (data access, exceptions, policy violations, and so forth). The data is grouped by entities. An entity is a set of related attributes, and an attribute is basically a field value.

For a description of all domains, see Domains. Each domain contains one or more entities. For a description of all entities and attributes, see Entities and Attributes.

A Guardium query returns data from one domain only. When the query is defined, one entity within that domain is designated as the main entity of the query. Each row of data returned by a query contains a count of occurrences of the main entity matching the values returned for the selected attributes, for the requested time period. This allows for the creation of two-dimensional reports from entities that do not have a one-to-one relationship.

There is a separate query builder for each domain. Access to each query builder is controlled by security roles. Each Guardium role typically has access to a subset of domains, depending on the function of that role within the company. Guardium admin role users typically have access to all reporting domains.

Some domains are available only when optional components (CAS, or Classification, for example) are installed. Other domains are available by default to Guardium admin role users only, for example report information pertaining to the Guardium appliance such as archiving activity.

Tuples

a tuple is one record (one row). The information in a database can be thought of as a spreadsheet, with columns (known as fields or attributes) representing different categories of information, and tuples (rows) representing all the information from each field associated with a single record.

cardinality

In DBMS you may hear cardinality term at two different places and it has two different meanings as well.

In Context of Data Models:
In terms of data models, cardinality refers to the relationship between two tables. Relationship can be of four types as we have already seen in Entity relationship guide:

One to One – A single row of first table associates with single row of second table. For example, a relationship between person and passport table is one to one because a person can have only one passport and a passport can be assigned to only one person.

One to Many – A single row of first table associates with more than one rows of second table. For example, relationship between customer and order table is one to many because a customer can place many orders but a order can be placed by a single customer alone.

Many to One – Many rows of first table associate with a single row of second table. For example, relationship between student and university is many to one because a university can have many students but a student can only study only in single university at a time.

Many to Many – Many rows of first table associate with many rows of second table. For example, relationship between student and course table is many to many because a student can take many courses at a time and a course can be assigned to many students.

Relational database design:

Key and types of keys:

What are Keys?

A DBMS key is an attribute or set of an attribute which helps you to identify a row(tuple) in a relation(table). They allow you to find the relation between two tables. Keys help you uniquely identify a row in a table by a combination of one or more columns in that table.

Example:

Employee ID FirstName LastName
11 Andrew Johnson
22 Tom Wood
33 Alex Hale

In the above-given example, employee ID is a primary key because it uniquely identifies an employee record. In this table, no other employee can have the same employee ID.

Various Keys in Database Management System

DBMS has folwing seven types of Keys each have their different functionality:

  • Super Key
  • Primary Key
  • Candidate Key
  • Alternate Key
  • Foreign Key
  • Compound Key
  • Composite Key
  • Surrogate Key

What is the Super key?

A superkey is a group of single or multiple keys which identifies rows in a table. A Super key may have additional attributes that are not needed for unique identification.

Example:

EmpSSN EmpNum Empname
9812345098 AB05 Shown
9876512345 AB06 Roslyn
199937890 AB07 James

In the above-given example, EmpSSN and EmpNum name are superkeys.

What is a Primary Key?

A column or group of columns in a table which helps us to uniquely identifies every row in that table is called a primary key. This DBMS can’t be a duplicate. The same value can’t appear more than once in the table.

Rules for defining Primary key:

  • Two rows can’t have the same primary key value
  • It must for every row to have a primary key value.
  • The primary key field cannot be null.
  • The value in a primary key column can never be modified or updated if any foreign key refers to that primary key.

Example:

In the following example, <code>StudID</code> is a Primary Key.

StudID Roll No First Name LastName Email
1 11 Tom Price abc@gmail.com
2 12 Nick Wright xyz@gmail.com
3 13 Dana Natan mno@yahoo.com

What is the Alternate key?

All the keys which are not primary key are called an alternate key. It is a candidate key which is currently not the primary key. However, A table may have single or multiple choices for the primary key.

Example: In this table.

StudID, Roll No, Email are qualified to become a primary key. But since StudID is the primary key, Roll No, Email becomes the alternative key.

StudID Roll No First Name LastName Email
1 11 Tom Price abc@gmail.com
2 12 Nick Wright xyz@gmail.com
3 13 Dana Natan mno@yahoo.com

What is a Candidate Key?

A super key with no repeated attribute is called candidate key.

The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key.

Properties of Candidate key:

  • It must contain unique values
  • Candidate key may have multiple attributes
  • Must not contain null values
  • It should contain minimum fields to ensure uniqueness
  • Uniquely identify each record in a table

Example: In the given table Stud ID, Roll No, and email are candidate keys which help us to uniquely identify the student record in the table.

StudID Roll No First Name LastName Email
1 11 Tom Price abc@gmail.com
2 12 Nick Wright xyz@gmail.com
3 13 Dana Natan mno@yahoo.com

What is the Foreign key?

A foreign key is a column which is added to create a relationship with another table. Foreign keys help us to maintain data integrity and also allows navigation between two different instances of an entity. Every relationship in the model needs to be supported by a foreign key.

Example:

DeptCode DeptName
001 Science
002 English
005 Computer
Teacher ID Fname Lname
B002 David Warner
B017 Sara Joseph
B009 Mike Brunton

In this example, we have two table, teach and department in a school. However, there is no way to see which search work in which department.

In this table, adding the foreign key in Deptcode to the Teacher name, we can create a relationship between the two tables.

Teacher ID DeptCode Fname Lname
B002 002 David Warner
B017 002 Sara Joseph
B009 001 Mike Brunton

This concept is also known as Referential Integrity.

What is the Compound key?

Compound key has many fields which allow you to uniquely recognize a specific record. It is possible that each column may be not unique by itself within the database. However, when combined with the other column or columns the combination of composite keys become unique.

Example:

OrderNo PorductID Product Name Quantity
B005 JAP102459 Mouse 5
B005 DKT321573 USB 10
B005 OMG446789 LCD Monitor 20
B004 DKT321573 USB 15
B002 OMG446789 Laser Printer 3

In this example, OrderNo and ProductID can’t be a primary key as it does not uniquely identify a record. However, a compound key of Order ID and Product ID could be used as it uniquely identified each record.

What is the Composite key?

A key which has multiple attributes to uniquely identify rows in a table is called a composite key. The difference between compound and the composite key is that any part of the compound key can be a foreign key, but the composite key may or maybe not a part of the foreign key.

What is a Surrogate Key?

An artificial key which aims to uniquely identify each record is called a surrogate key. These kind of key are unique because they are created when you don’t have any natural primary key. They do not lend any meaning to the data in the table. Surrogate key is usually an integer.

Fname Lastname Start Time End Time
Anne Smith 09:00 18:00
Jack Francis 08:00 17:00
Anna McLean 11:00 20:00
Shown Willam 14:00 23:00

Above, given example, shown shift timings of the different employee. In this example, a surrogate key is needed to uniquely identify each employee.

Difference Between Primary key & Foreign key

Primary Key Foreign Key
Helps you to uniquely identify a record in the table. It is a field in the table that is the primary key of another table.
Primary Key never accept null values. A foreign key may accept multiple null values.
Primary key is a clustered index and data in the DBMS table are physically organized in the sequence of the clustered index. A foreign key cannot automatically create an index, clustered or non-clustered. However, you can manually create an index on the foreign key.
You can have the single Primary key in a table. You can have multiple foreign keys in a table.

Relational integrity rules:

Integrity rule 1: Entity integrity

It says that no component of a primary key may be null.

All entities must be distinguishable. That is, they must have a unique identification of some kind. Primary keys perform unique identification function in a relational database. An identifier that was wholly null would be a contradiction in terms. It would be like there was some entity that did not have any unique identification. That is, it was not distinguishable from other entities. If two entities are not distinguishable from each other, then by definition there are not two entities but only one.

Integrity rule 2: Referential integrity

The referential integrity constraint is specified between two relations and is used to maintain the consistency among tuples of the two relations.

Suppose we wish to ensure that value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another. This is referential integrity.

The referential integrity constraint states that, a tuple in one relation that refers to another relation must refer to the existing tuple in that relation. This means that the referential integrity is a constraint specified on more than one relation. This ensures that the consistency is maintained across the relations.Table A

DeptIDDeptNameDeptManager
F-1001FinancialNathan
S-2012SoftwareMartin
H-0001HRJason

Table B

EmpNoDeptIDEmpName
1001F-1001Tommy
1002S-2012Will
1003H-0001Jonathan

Codd’s rules

Rule 0: The foundation rule: For any system that is advertised as, or claimed to be, a relational data base management system, that system must be able to manage data bases entirely through its relational capabilities.

Rule 1: The information rule: All information in a relational data base is represented explicitly at the logical level and in exactly one way – by values in tables.

Rule 2: The guaranteed access rule: Each and every datum (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.

Rule 3: Systematic treatment of null values: Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.

Rule 4: Dynamic online catalog based on the relational model: The data base description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.

Rule 5: The comprehensive data sublanguage rule: A relational system may support several languages and various modes of terminal use (for example, the fill-in-the-blanks mode). However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and that is comprehensive in supporting all of the following items:

  1. Data definition.
  2. View definition.
  3. Data manipulation (interactive and by program).
  4. Integrity constraints.
  5. Authorization.
  6. Transaction boundaries (begin, commit and rollback).

Rule 6: The view updating rule: All views that are theoretically updatable are also updatable by the system.

Rule 7: Possible for high-level insert, update, and delete: The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data but also to the insertion, update and deletion of data.

Rule 8: Physical data independence: Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representations or access methods.

Rule 9: Logical data independence: Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically permit unimpairment are made to the base tables.

Rule 10: Integrity independence: Integrity constraints specific to a particular relational data base must be definable in the relational data sublanguage and storable in the catalog, not in the application programs.

Rule 11: Distribution independence: The end-user must not be able to see that the data is distributed over various locations. Users should always get the impression that the data is located at one site only.

Rule 12: The nonsubversion rule: If a relational system has a low-level (single-record-at-a-time) language, that low level cannot be used to subvert or bypass the integrity rules and constraints expressed in the higher level relational language (multiple-records-at-a-time).

Leave a Reply

Your email address will not be published. Required fields are marked *