Databases
Chapter 1: ER Model
and Relational Model 4.3
Chapter 2: Structured Query Language 4.21
Chapter 3: Normalization 4.49
Chapter 4: Transaction and Concurrency
4.65
Chapter 5: File Management 4.82
U
n
i
t
4
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Guidelines and tips
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
dbms notes complete full, Summaries of Database Programming
great notes dbms notes complete full dbms notes complete full
Typology: Summaries
2025/2026
1 / 97
This page cannot be seen from the preview
Don't miss anything!
Related documents
Partial preview of the text
Download dbms notes complete full and more Summaries Database Programming in PDF only on Docsity!
Databases
Chapter 1: ER Model
and Relational Model 4.
Chapter 2: Structured Query Language 4.
Chapter 3: Normalization 4.
Chapter 4: Transaction and Concurrency
Chapter 5: File Management 4.
U
n
i
t
Chapter 1
ER Model and Relational Model
intRodUCtion
A database is a collection of related data. By data, we mean facts that can be recorded and that have implicit meaning.
Example: Consider the names, telephone numbers and addresses of the people. We can record this data in an indexed address book and store it as Excel fi le on a hard drive using a personal computer. This is a collection of related data with an implicit meaning and hence is a database. A database management system (DBMS) is a collection of pro- grams that enables users to create and maintain a database. The DBMS is a general-purpose software system that facilitates the processes of defi ning, constructing, manipulating and sharing databases among various users and applications.
- Defining the database involves specifying the data types, structures, and constraints for the data to be stored in the database.
- Constructing the database is the process of storing the data itself on some storage medium that is controlled by the DBMS
- Manipulating a database includes such functions as querying the database to retrieve specifi c data, updating the database to refl ect changes.
- Sharing a database allows multiple users and programs to access the database concurrently.
- Fundamental characteristics of the database approach is that it provides some level of data abstraction by hiding details of data storage that are not needed by users.
Data Model
A data model is a collection of concepts that can be used to describe the structure of a database. It provides the necessary means to achieve abstraction.
sChEMas
In any data model, it is important to distinguish between the description of the database and the database itself. The descrip- tion of a database is called the database schema , which is specified during database design and is not expected to change frequently. The actual data in a database may change frequently, for exam- ple the student database changes every time we add a student or enter a new grade for a student. The data in the database at a par- ticular moment in time is called a database state or snapshot. It is also called the current set of occurrences or instances in the database. The distinction between database schema and database state is very important. When we defi ne a new database, we specify its database schema only to the DBMS. At this point, the correspond- ing database state is the empty state with no data. We get the ini- tial state of the database when the database is fi rst loaded with the initial data. The DBMS stores the description of the schema constructs and constraints, also called the metadata in the DBMS catalog so that DBMS software can refer to the schema whenever it needs. The schema is sometimes called the intension , and the database state an extension of the schema.
Data model Schemas Three-schema architecture ER model Types of attributes Mapping cardinality Complex attributes
Entity types, entity sets and value sets Weak entity set Relational database NULL in tuples Inherent constraint Referential and entity integrity constraint
LEARNING OBJECTIVES
Chapter 1 • ER Model and Relational Model | 4.
Address
Street address City State Zip
Number Street House-No Figure 1 A hierarchy of composite attributes.
Street address is a composite attribute. Attributes that are not divisible are called simple (or) atomic attributes.
Single-valued versus multivalued attributes : Most attributes have a single value for a particular entity, such attributes are called single-valued attribute.
Example: Age is a single-valued attribute
Multivalued attributes : An attribute can have a set of values for the same entity.
Example: College degrees attribute for a person Example: Name is also a multivalued attribute (Figure 2).
Name
First name Middle name
Last name
Figure 2 Multivalued attribute.
Stored versus derived attributes : Two (or) more attribute values are related.
Example: Age can be derived from a person’s date of birth.
The age attribute is called derived attribute and is said to be derivable from the DOB attribute, which is called a stored attribute.
Domain : The set of permitted values for each attribute.
Example: A person’s age must be in the domain {0-130}
RElationship sEts
A relationship is an association among several entities. Relationship sets that involve two entity sets are binary. Generally, most relationships in databases are binary. Relationship sets may involve more than two entity sets.
Example: Employee of a bank may have responsibilities at multiple braches, with different jobs at different branches, then there is a ternary relation between employee, job and branch.
Mapping Cardinality
For a binary relationship set, mapping cardinality must be:
- One-to-one
- One-to-many
- Many-to-one
- Many-to-many
One-to-one : An entity in A is associated with at most one entity in B and an entity in B is associated with at most one entity in A (Figure 3).
a 1 a 2 a 3 a 4
b 1 b 2 b 3 b 4
Figure 3 One-to-one relationship set. One-to-many : An entity in A is associated with any number of entities in B. But an entity in B is associated with at most one entity in A (Figure 4).
a 1 a 2 a 3
b 1 b 2 b 3 b 4 b 5
Figure 4 One-to-many relationship set. Many-to-one : An entity in A is associated with at most one entity in B. But an entity in B can be associated with any number of entities in A (Figure 5).
b 1 b 2 b 3
a 1 a 2 a 3 a 4 a 5
Figure 5 Many-to-one relationship set. Many-to-many : An entity in A is associated with any num- ber of entities in B. But an entity in B can be associated with any number of entities in A (Figure 6).
a 1 a 2 a 3 a 4
b 1 b 2 b 3 b 4
Figure 6 Many-to-many relationship set. Example: One customer can have multiple accounts Customer(c-Name) (Acc. no, Amount) Table 3 Example of One-to-many Relationship Set
Arun Bunny Kate Mary John
A-101 $ A- A- A- A-
$ $ $ $
In Table 3, many-to-one relationship is not possible.
4.6 | Unit 4 • Databases
Complex Attributes
Composite and multivalued attributes can be nested in an arbitrary way. We can represent arbitrary nesting by group- ing components of a composite attribute between parenthe- ses () and separating the components with commas, and by displaying multivalued attributes between braces { }. Such attributes are called complex attributes.
Example: A person can have more than one residence and each residence can have multiple phones, an attribute AddressPhone for a person can be specified as shown below. {AddressPhone
({Phone (Areacode, phoneNumber)}, Address (StreetAddress (StreetNumber, streetName, ApartmentNumber), city, state, zip))}
Entity types, entity sets and value sets
An entity type defines a collection of entities that have the same attributes. Each entity type in the database is described by its name and attribute. The following figure shows two entity types, named STUDENT and EMPLOYEE and a list of attributes for each
ENTITY TYPE NAME ATTRIBUTES:
ENTITY SET
(EXTENSION)
STUDENT R.No, Name, Grade
EMPLOYEE Name, Salary, Age
S 1. (86, Arun, A ) S 2. (87, Pavan, B ) S 3. (89, Karan, A )
e 1. (Kamal, 20K, 42) e 2. (Bharat, 25K, 41) e 3. (Bhanu, 26K, 41)
The collection of all entities of a particular entity type in the database at any point in time is called an entity set.
Types of relations
- Unary relation. 4. Quadnary relation.
- Binary relation. 5. N-ary relation
- Ternary relation.
Number of entity types Degree
Cardinality Optionality
- Unary
- Binary
- Ternary
N–ary
1 : 1 1 : N N : 1 M : N
Optional Mandatory
Relationship
Unary relation If a relationship type is between entities in a single entity type then it is called a unary relationship type.
Employee Managed-by
In employee entity, we will have all the employees including ‘manager’, this relation indicates, employees are managed by manager.
Binary relation If a relationship type is between entities in one type and entities in another type then it is called a binary relation , because two entity types are involved in the relation.
Customer Purchased Product
The above relation indicates that customers purchased prod- uct (or) products are purchased by customers.
Quadnary relation If a relationship type is among entities of four different types, then it is called quadnary relation.
Student Register Course
Department
Lecturer
In the above ER-diagram, any two entities can have a relation.
N-ary relation ‘ N ’ number of entities will participate in a relation, and each entity can have a relation with all the other entities.
4.8 | Unit 4 • Databases
E
1 N
R (^) E
E R^ E
R (^) E
Total Participation of E 2 in R 1
Cardinality ratio 1: n
(min, max)
Structural constraint on participation of E In R
Cardinality Constraints
One-to-one
Each entity of one entity set is related to at most one entity of the other set. Only one matching record exists between two tables.
Example: Assume each owner is allowed to have only one dog and each dog must belong to one owner. The own relationship between dog and owner is one-to-one. One-to- one relationships can often combine the data into one table.
Examples:
- One birdfeeder is located in one place in the yard.
- One state has one governor.
- One yard has one address.
- One patient has one phone number.
- One student has one ID.
Customer Borrower^ Loan
One customer is associated to one loan via borrower
One-to-many
Examples:
- One birdfeeder is visited by many birds.
- One student can have many degrees.
- One Book can be written by many authors.
- One yard contains many bird feeders.
- One patient has many prescriptions.
In the one-to-many relationship, a loan is associated with one customer via borrower.
Many-to-one
Customer Borrower Loan
A customer is associated with at most one loan via borrower.
Many-to-many
Examples:
- Many students are taught by many teachers.
- Many patients are treated by many doctors.
- Many medications are taken by many patients.
- Many customers buy many products.
- Many books are written by many authors.
Customer Borrower Loan
Customer is associated with several loans and loan is asso- ciated with several customers.
ER Diagram with a Ternary
Relationship
Employee Works-on Branch
E-name (^) Street E-id City
B-city Assets
Job
Title Level
B-name
Figure 9 ER diagram with a ternary relationship.
Weak Entity Set
An entity set that does not have a primary key is called weak entity set. Weak entity set is represented by → Underline the primary key of a weak entity with a dashed line. Example:
Loan
Loan payment Payment
L-no (^) Amount
P-date
Amount P-no
RElational databasE
Relational Model
The relational model represents the database as a collection of relations. When a relation is thought of as a table of val- ues, each row in the table represents a collection of related data values. Each row in the table represents a fact that typi- cally corresponds to a real-world entity. The table name and
Chapter 1 • ER Model and Relational Model | 4.
column names are used to help in interpreting the meaning of the values in each row. In formal relational model terminology, a row is called a tuple , a column header is called an attribute , and the table is called a relation.
Domain
A domain is a set of atomic values. A common method of specifying a domain is to specify a data type from which the data values forming the domain are drawn. It is also useful to specify a name for the domain, to help in interpreting its values
Example:
- Set of telephone numbers : The set of valid numbers in a particular country.
- Employee id numbers : The set of valid employee numbers in a company.
- Names : The set of character strings that represent names of persons.
- Grade-point average : Possible values of computed grade point averages, each must be real (floating point) number between 0 and 4
- Research department names : The set of research department names in a specialization, such as computer science, chemistry and applied mathematics.
- Research department codes : The set of Research department codes, such as CS, CHE, AM. The preceding is called logical definitions of domains. The data type for research department names is set of character strings that represent valid department names. A domain is thus given a name, data type, and format. Additional information for interpreting the values of a domain can also be given, for example a numeric domain such as person weights should have the units of measure- ments, such as kilograms or pounds.
- The relational model is often described as having the following three aspects:
- Structural aspect : The data in the database is per- ceived by the user as tables.
- Integrity aspect : Those tables has to satisfy certain integrity constraints.
- Manipulative aspect : The operators available to the user for manipulating those tables, for purposes of data retrieval, these operators derive tables form tables, the most important operators are ‘SELECT’, ‘PROJECT’ and JOIN.
Relation Schema
A relation schema ‘ R ’ denoted by R ( A 1 , A 2 ,... A (^) n ) is made up of a relation name R and a list of attributes A 1 , A 2 ,... An. Each Attribute Ai is the name of role played by some domain D in the relation schema R. D is called the domain of Ai and is denoted by dom( Ai ). A relation schema is used to describe a relation and R is called the name of this relation. The degree of a relation is the number of attributes ‘ n ’ of its relation schema.
Example: A relation schema of degree ‘7’, which describes an employee is given below: EMPLOYEE (Name, EId, HomePhone, Address, Office phone, Age, Salary) Using the data type of each attribute, the definition is written as: EMPLOYEE (Name: String, EId: INT, Homephone: INT, Address: String, OfficePhone: String, Age: Real, Salary: INT) For this relation schema, EMPLOYEE is the name of the relation, which has ‘7’ attributes.
- A relation ‘ r ’ of the relation schema R ( A 1 , A 2... A (^) n ), also denoted by r ( R ), is a set of n-tuples. r = { t 1 , t 2... t (^) m }. Each n -tuple ‘ t ’ is an ordered list of n values t = < V 1 , V 2... N (^) n >, where each value Vi , 1 ≤ i... n , is an element of dom( A (^) i ) or is a special null value.
- The ith value in tuple t , which corresponds to the attribute Ai , is referred to as t [ Ai ].
- The following figure shows EMPLOYEE relation. Each type in a relation represents a particular employee entity. We display the relation as a table where each tuple is shown as a row and each attribute corresponds to a column header, indicating a role or interpretation of the values in that column. Null values represent attributes whose values are unknown or do not exist for some individual EMPLOYEE tuple.
Employee
Name EId
Home Phone Address
Office Phone Age Salary Mahesh 30-01 870-223366 Warangal NULL 35 40 k Ramesh 30-02 040-226633 Hyderabad NULL 36 40 k Suresh 30-03 040-663322 Kolkata 040-331123 35 42 k Dinesh 30-04 040-772299 Bangalore 040-321643 36 40 k
Chapter 1 • ER Model and Relational Model | 4.
- Any such set of attributes SK is called a super key of the relation schema R. SK specifies a uniqueness constraint that no two distinct tuples in any state r or R can have the same value for SK.
- Every relation has at least one default super key, the set of all its attributes. A key, ‘ K ’ of a relation schema R is a super key of R with the additional property that removing any attributes ‘ X ’ from K leaves a set of attributes K ’ that is not a super key of R any more.
A key satisfies the following two constraints:
- Two distinct tuples in any state of the relation cannot have identical values for all the attributes in the key.
- A super key from which we cannot remove any attributes and still have the uniqueness constraints mentioned in above condition is known as a minimal super key. The first condition applies to both keys and super keys. The second condition is required only for keys.
Example: Consider the employee relation in Page no. 9. The attribute set {EId} is a key of employee because no two employee tuples can have the same value for EId. Any set of attributes that include EId will form a super key.
- {EId, Homephone, Name}
- {EId, Age, Salary}
- {Name, EId, Address}
However, the super key {EId, Name, Age} is not a key of EMPLOYEE, because removing Name or age or both from the set leaves us with a super key. Any super key formed from a single attributes is also a key. A key with multiple attributes must require all its attributes to have the uniqueness property. A relation schema may have more than one key. In that case, each of the keys is called a candidate key.
Example: Employee relation has three candidate keys. {Name, EId, Homephone}
One of the candidate keys is chosen as primary key of the relation.
Another constraint on attributes specifies whether null values are permitted in tuples or not. If we want some tuples to have a valid (or) non-null value, we need to use NOT NULL constraint on that attribute.
Referential and entity integrity constraint
The entity Integrity constraint states that no primary key value can be null. If we have NULL values in the primary key column, we cannot identify some tuples in a relation.
- Key constraints and entity Integrity constraints are specified on individual relations
- Referential integrity constraint is specified between relations and used to maintain the consistency among tuples in the two relations.
- Referential Integrity constraints states that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation.
- To understand the concept of Referential Integrity, first we have to understand the concept of FOREIGN KEY.
- Suppose we have two relations R 1 and R 2. A set of attributes FK in relation schema R 1 is a foreign key of R 1 that references relation R 2 if it satisfies the following two rules:
- The attributes in FK have the same domains as the primary key attributes PK of R 2 , FK will have to refer to PK.
- A value of FK in a tuple t 1 of the current state r 1 ( R 1 ) either occurs as a value of PK for some tuple t 2 in the current state r 2 ( R 2 ) or is null. We have t 1 [FK] = t 2 [PK], and we say that the tuple t 1 references to the tuple t 2. In this definition, R 1 is called the ref- erencing relation and R 2 is the referenced relation.
- A foreign key can refer to its own relation. We can diagrammatically display referential integrity constraints by drawing a directed Arc from each foreign key to the relation it references. The arrow head may point to the primary key of the referenced relation.
Example:
FNAME LNAME EID DOB GENDER SALARY DNO
EMPLOYEE
DNO DNAME DLOCATION DMANAGER
DEPARTMENT
EID DEPENDENT-NAME GENDER DOB RELATIONSHIP
DEPENDENT
EID PROJ-NO HOURS
WORKS
4.12 | Unit 4 • Databases
- Referential integrity rule : The database must not contain any unmatched foreign key values.
If ‘ B ’ References ‘ A ’, then A must exist.
NOT NULL constraint
- NOT NULL constraint restricts a column from having a NULL value. NOT NULL constraint can be applied to any column in a table.
- We cannot give NULL values under that column
- NOT NULL Constraint enforces a column to contain a proper value.
- This constraint cannot be defined at table level.
Example: CREATE TABLE student(RNo:INT Name:varchar(70) NOT NULL age:INT) Suppose a row is inserted into the following table,
Insert into student values <11, NULL, 20>
In the schema, we enforced NOT NULL constraint on Name column, means Name cannot have NULL value, when the above insert command is executed, the system gives, NOT NULL constraint violation.
UNIQUE constraint
The column on which UNIQUE constraint is enforced should not have any duplicate values.
- UNIQUE constraint can be enforced on any column except the primary key column.
- By default primary key column will not accept any duplicate values that are handled by key constraint.
- UNIQUE constraint can be applied at column level or table level.
Example: CREATE TABLE student (RNo:INT Name:varchar(60) Grade:CHAR(1)) Assume that the table contains following tuples
Student R no. Name Grade 11 12 13
Sita Anu Bala
B A A
Suppose the following tuple is inserted into the student table.
- Insert into student values <14, ‘Anu’, ‘ B ’>
- UNIQUE constraint is enforced on Name column, in the student table we have ‘Anu’, and again the new tuple contains name ‘Anu’, this Insert command violates the UNIQUE constraint.
CHECK constraint
This constraint is used to restrict a value of a column between a range.
- When a value is inserted into particular column, before storing that, a check will be performed to see whether the values lie within the specified range.
- If the value entered is out of range, it will not accept and violation happens. 3. It is like checking a condition before saving data into a column. Example: Create table student (RNo:INT CHECK (Rno>0) Name:varchar(60) Dept:varchar(4)) Suppose the following tuple is inserted into student table.
- Insert into student values <-4, ‘Bhanu’, ‘CS’>
- CHECK constraint is enforced on RNo column, the RNo should be greater than ‘0’, but ‘-4’ is given.
- CHECK constraint is violated. Creating table from a table : A view is called a derived table (or) virtual table , because the data stored in views is taken from already existing tables. A view can also be defined as a logical subset of data from one or more tables. Syntax : CREATE view view-name As SELECT column-names FROM table-name WHERE condition Example: Consider the following table “sales”. Sales
Order-Id
Order Name
Previous Balance Customer 21 22 23 24 25 26
Order 3 Order 4 Order 5 Order 6 Order 7 Order 8
3000 1000 3000 2000 2000 4000
Ana Adam Brat John Ana Ana Query to create a view : CREATE view sales-view As SELECT * FROM Sales WHERE customer = ‘Ana’
- The data fetched from select statement will be stored in an object called ‘sales-view’.
- To display the contents stored in view, execute the following statement. SELECT * FROM Sales-view Removal of specific rows : Consider the following SQL query: Delete * FROM Sales The above query will delete all the tuples from sales. To remove specific rows, we have to specify the condi- tion in WHERE clause. Consider the table “sales” given in the above example. Remove the rows from sales table whose previous bal- ance is 3000.
4.14 | Unit 4 • Databases
We can achieve this effect by extending the foreign key as indicated below:
CREATE TABLE SHIPMENT{.....}.......
FOREIGN KEY {SUPPLIER - NUMBER}
REFERENCES
SUPPLIER ON DELETE CASCADE
The specification ON DELETE CASCADE defines a delete rule for this particular foreign key, and the speci- fication CASCADE is the referential action for that delete rule. The meaning of these specifications is that a DELETE operation on the suppliers relvar will ‘Cascade” to delete matching tuples (if any) in the shipments relvar as well. Same procedure is applied for all the referential actions.
tRiggERs
Triggers are precompiled procedures that are stored along with the database and invoked automatically whenever some specified event occurs. Suppose we have a view called HYDERABAD - SUPPLIER defined as follows:
CREATE VIEW HYDERABAD-SUPPLIER
AS SELECT SUPPLIER - NUMBER, SUPPLIER-
NAME, STATUS
FROM SUPPLIER
WHERE CITY = ‘HYDERABAD’,
Normally, if the user tries to insert a row into this view, SQL will actually insert a row into the underlying base table SUPPLIERS with CITY value whatever the default is for the CITY column. Assuming that default is not Hyderabad, the net effect is that the new row will not appear in the view; therefore, let us create a triggered pro- cedure as follows:
CREATE TRIGGER HYDERABAD -
SUPPLIER - INSERT
INSTEAD OF INSERT ON HYDERABAD
- SUPPLIER
REFERENCING NEW ROW AS R
FOR EACH ROW
INSERT INTO SUPPLIERS (SUPPLIER -
NUMBER, SUPPLIER - NAME, STATUS, CITY)
VALUES (R. SUPPLIER - NUMBER, R.
SUPPLIER - NAME, R. STATUS, ‘HYDERABAD’);
Inserting a row into the view will now cause a row to be inserted into the underlying base table with CITY value equal to Hyderabad inserted of the default value.
In general, CREATE TRIGGER specifies, among other things, an event, a condition, and an action. The event is an operation on the database (“INSERT ON HYDERABAD - SUPPLIER” in the example)
- The “condition” is a Boolean expression that has to evaluate to TRUE in order for the action to be executed.
- The ‘action’ is the triggered procedure (“INSERT INTO SUPPLIERS.. .)
- The event and condition together are sometimes called the triggering event. The combination of all three (event, condition, and action) is usually called a trigger.
- Possible events include INSERT, DELETE, UPDATE, reaching end-of-transaction (COMMIT) reaching a specified time of day, exceeding a specified elapsed time, violating a specified constraint, etc.
- A database that has associated triggers is sometimes called an active database.
Base Table Constraints
SQL-base table constraints are specified on either CERATE TABLE or ALTER TABLE. Each such constraint is a can- didate key constraint, a foreign key constraint, or a CHECK constraint. Candidate keys : An SQL candidate key definition takes one of the following two forms:
PRIMARY KEY (< column name comma list>) UNIQUE (< column name comma list>) The following example illustrates base table con- straints of all three kinds: CREATE TABLE SHIPMENTS (SUPPLIER-NUMBER. SUPPLIER-NUMBER NOT NULL, PART - NUMBER PART-NUMBER NOT NULL, QUANTITY NOT NULL PRIMARY KEY (SUPPLIER - NUMBER, PART NUMBER) FOREIGN KEY (SUPPLIER-NUMBER) REFERENCES SUPPLIERS ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (PART-NUMBER) REFERENCES PARTS ON DELETE CASCADE ON UPDATE CASCADE CHECK(QUANTITY ≤ QUANTITY (0) AND QUANTITY ≤ QUANTITY (1000));
A check constraint of the form CHECK (< column name > IS NOT NULL) can be replaced by a simple NOT NULL specification in the definition of the column.
Chapter 1 • ER Model and Relational Model | 4.
ExERCisEs
Practice Problems 1
Directions for questions 1 to 20: Select the correct alterna- tive from the given choices.
1. Consider the following two tables T 1 and T 2. Show the output for the following operations: Table T 1 P Q R 11 a 6 16 b 9 26 a 7
Table T 2
A B C 11 b 7 26 c 4 11 b 6
What is the number of tuples present in the result of given algebraic expressions? (i) T 1 ⋈ T 1. P = (^) T 2. AT 2 (A) 2 (B) 3 (C) 4 (D) 5 (ii) T 1 ⋈ T 1. Q = (^) T 2. BT 2 (A) 2 (B) 3 (C) 4 (D). (iii) T 1 ⋈( T 1. p = T 2. A AND T 1. R = T 2. C )T 2 (A) 1 (B) 2 (C) 3 (D).
2. Suppose R 1 ( A , B ) and R 2 ( C , D ) are two relation schemas. Let R 1 and R 2 be the corresponding relation instances. B is a foreign key that refers to C in R 2. If data in R 1 and R 2 satisfy referential integrity constraints, which of the following is true? (A) π (^) B ( R 1 (^) ) − π (^) c ( R 2 )=φ (B)^ π^ C (^ R^ 2 )^ −^ π^ B ( R^1 )=φ (C) π (^) B ( R 1 (^) ) − π (^) C ( R 2 )≠φ
(D) Both A and B
3. Consider the following relations: A , B and C A Id Name Age 12 Arun 60 15 Shreya 24 99 Rohit 11
B
Id Name Age 15 Shreya 24 25 Hari 40 98 Rohit 20 99 Rohit 11 C Id Phone Area 10 2200 02 99 2100 01
How many tuples does the result of the following rela- tional algebra expression contain? Assume that the scheme of ( A ∪ B ) is the same as that of A. ( A ∪ B )⋈ A. Id >40 v c. Id <15 C (A) 6 (B) 7 (C) 8 (D) 9
4. Consider the relations A , B and C given in Question 3. How many tuples does the result of the following SQL query contain? SELECT A.Id FROM A WHERE A.Age> ALL (SELECT B.Age FROM B WHERE B.Name = ‘Arun’)? (A) 0 (B) 1 (C) 2 (D) 3 5. Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record ( X = 1, Y = 1) is inserted in the table. Let MX and MY denote the respective maximum value of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX + 1, 2 * MY + 1, respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above are carried out? SELECT Y FROM T WHERE X = 7;? (A) 15 (B) 31 (C) 63 (D) 127 6. Database table by name loan records is given below:
Borrower Bank Manager Loan Amount Ramesh Sunderajan 10000 Suresh Ramgopal 5000 Mahesh Sunderajan 7000
Chapter 1 • ER Model and Relational Model | 4.
(A) 0, 1 (B) 1, 3
(C) 1, 5 (D) 0, 3
13. Given {customer} is a candidate key, [customer name, customer street} is another candidate key then (A) {customer id, customer name} is also a candidate key. (B) {customer id, customer street} is also a candidate key. (C) {customer id, customer name, customer street} is also a candidate key. (D) None
Common data for questions 14 and 15: Consider the fol- lowing diagram,
X
Y Z
R 1
X 1 X (^2) X 3
Z 1
Z 1
Y 1
Y 2
R 1
14. The minimum number of tables needed to represent X , Y , Z , R 1 , R 2 is (A) 2 (B) 3 (C) 4 (D) 5 15. Which of the following is a correct attribute set for one of the tables for the correct answer to the above questions? (A) { X 1 , X 2 , X 3 , Y 1 } (B) { X 1 , Y 1 , Z 1 , Z 2 } (C) { X 1 , Y 1 , Z 1 } (D) { M 1 , Y 1 } 16. UPDATE account SET
DA = basic * .2, GROSS = basic * 1.3, Where basic > 2000;
(A) The above query displays DA and gross for all those employees whose basic is ≥ 2000 (B) The above query displays DA and gross for all em- ployees whose basic is less than 2000 (C) The above query displays DA as well as gross for all those employees whose basic is > (D) Above all
17. Which of the following query transformations is correct? R 1 and R 2 are relations C 1 , C 2 are selection conditions and A 1 and A 2 are attributes of R 1 (A) σ C 1 (σ C 1 ( R 1 ))→σ C 2 ( σ C 2 ( R 1 )) (B) σ C 1 (σ A 1 ( R 1 ))→σ A 1 ( σ C 1 ( R 1 )) (C) p A 2 (p A ( R 1 ))→p A (p A 2 ( R 1 )) (D) All the above 18. Consider the following query select distinct a 1 , a 2 , … a (^) n from r 1 , r 2... rm where P for an arbitrary predicate P , this query is equivalent to which of the following rela- tional algebra expressions: (A) π σ a a (^) n p^ m r r r 1 ...^1
( × × ... × )
(B) π σ a a (^) n p^ m r r r r 1 ...^1 2
( × × ×… ×. )
(C) σ π a a (^) n p^ m r r r 1 ...^1
( × × ... × )
(D) σ π a a (^) n p^ m r r r 1 ...^1
( × ×…× )
19. The relational algebra expression equivalent to the fol- lowing tuple calculus expression { a } a ∈ r (^) ^ ( a [ A ] = 10 ^ a [ B ] = 20) is (A) σ( A = 10 r B = 20)r (B) σ( A = 10 ) ( r ) ∪ σ( B = 20) ( r ) (C) σ( A = 10 ) ( r ) ∩ σ( B = 20) ( r ) (D) σ( A = 10 ) ( r ) – σ( B = 20) ( r ) 20. Which of the following is/are wrong? (A) An SQL query automatically eliminates dupli- cates. (B) An SQL query will not work if there are no in- dexes on the relations. (C) SQL permits attribute names to be repeated in the same relation (D) All the above
Practice Problems 2
Directions for questions 1 to 20: Select the correct alterna- tive from the given choices.
1. If ABCDE are the attributes of a table and ABCD is a super key and ABC is also super key then (A) A B C must be candidate key (B) A B C cannot be super key (C) A B C cannot be candidate key (D) A B C may be candidate key 2. The example of derived attribute is (A) Name if age is given as other attribute (B) Age if date_of_birth is given as other attribute (C) Both (A) and (B) (D) None 3. The weak entity set is represented by (A) box (B) ellipse (C) diamond (D) double outlined box
4.18 | Unit 4 • Databases
4. In entity relationship diagram double lines indicate (A) Cardinality (B) Relationship (C) Partial participation (D) Total participation 5. An edge between an entity set and a binary relationship set can have an associated minimum and maximum cardinality, shown in the form 1… h where 1 is the minimum and h is the maximum cardinality A mini- mum value 1 indicates: (A) total participation (B) partial participation (C) double participation (D) no participation 6. Let R be a relation schema. If we say that a subset k of R is a super key for R , we are restricting R , we are restricting consideration to relations r ( R )in which no two district tuples have the same value on all attributes in K. That is if t 1 and t 2 are in r and t 1 ≠ t 2 (A) t 1 [ k ] = 2 t 2 [ K ] (B) t 2 [ K ] = 2 t 1 [ k ] (C) t 1 [ k ] = t 2 [k] (D) t 1 [k] ≠ t 2 [k] 7. Which one is correct? (A) Primary key ⊂ Super key ⊂ Candidate key (B) Candidate key ⊂ Super key ⊂ Primary key (C) Primary key ⊂ Candidate key ⊂ Super key (D) Super key ⊂ Primary key ⊂ Candidate key 8. If we have relations r 1( R 1) and r 2( R 2), then r 1 ( r 2 is a relation whose schema is the (A) concatenation (B) union (C) intersection (D) None 9. Match the following: I Empid 1 Multivalued II Name 2 Derived III Age 3 Composite IV Contact No. 4 Simple
(A) I – 4, II – 3, III – 2, Iv – 1 (B) I – 3, II – 2, III – 4, Iv – 1 (C) I – 2, II – 1, III – 4, Iv – 3 (D) I – 1, II – 3, III – 2, IV – 4
10. Match the following:
I Double-lined ellipse 1 Multivalued attribute II Double line 2 Total participation III Double-lined box 3 Weak entity set IV Dashed ellipse 4 Derived attribute (A) I – 1, II – 2, III – 3, IV – 4 (B) I – 2, II – 3, III – 4, IV – 1 (C) I – 3, II – 4, III – 2, IV – 2 (D) I – 4, II – 3, III – 2, IV –
11. The natural join is a (A) binary operation that allows us to combine certain selections and a Cartesian product into one operation (B) unary operations that allows only Cartesian product
(C) query which involves a Cartesian product and a projection (D) None
12. The number of entities participating in the relationship is known as (A) maximum cardinality (B) composite identifiers (C) degree (D) None 13 A minimum cardinality of 0 specifies (A) non-participation (B) partial participation (C) total participation (D) zero participation 14. What is not true about weak entity? (A) They do not have key attributes. (B) They are the examples of existence dependency. (C) Every existence dependency results in a weak entity (D) Weak entity will have always discriminator attributes 15. Which one is the fundamental operation in the rela- tional algebra? (A) Natural join (B) Division (C) Set intersection (D) Cartesian product 16. For the given tables
A B X Y Y a 1 b 1 b 1 a 2 b 1 b 2 a 1 b 2 a 2 b 2
A ÷ B will return (A) a 1 , a 2 (B) a 1 (C) a 2 (D) None
17. The number of tuples selected in the above answer is (A) 2 (B) 1 (C) 0 (D) 4
Common data for questions 18 and 19: Consider the fol- lowing schema of a relational database. Emp (empno, name, add) Project (Pno, Prame) Work on (empno, Pno) Part (partno, Pname, qty, size) Use (empno, pno, partno, no)
18. ((name(emp) ( (name(emp ⋈^ workon) displays (i) The names of the employees who are not working in any project (ii) The names of the employees who were working in every project. (A) Only (i) (B) Only (ii) (C) Both (A) and (B) (D) None
4.20 | Unit 4 • Databases
answER KEys
ExERCisEs
Practice Problems 1
1. (i) B (ii) A (iii) A 2. A 3. B 4. D 5. D 6. D 7. B 8. B 9. B 10. A 11. A 12. B 13. D 14. A 15. A 16. C 17. B 18. B 19. C 20. D
Practice Problems 2
1. D 2. B 3. C 4. A 5. A 6. D 7. C 8. A 9. A 10. A
11. A 12. C 13. C 14. A 15. D 16. A 17. A 18. A 19. C 20. B
Previous Years’ Questions
1. B 2. C 3. D 4. B 5. A 6. A 7. C 8. C 9. A
8. An ER model of a database consists of entity types A and B. These are connected by a relationship R which does not have its own attribute, Under which one of the following conditions, can the relational table for R be merged with that of A? [2017] (A) Relationship R is one-to-many and the participa- tion of A in R is total. (B) Relationship R is one-to-many and the participa- tion of A in R is partial. (C) Relationship R is many-to-one and the participa- tion of A in R is total. (D) Relationship R is many-to-one and the participa- tion of A in R is partial. 9. In an Entity-Relationship (ER) model, suppose R is a many-to-one relationship from entity set E 1 to entity
set E 2. Assume that E 1 and E 2 participate totally in R and that the cardinality of E 1 is greater than the cardinality of E 2. Which one of the following is true about R? [2018] (A) Every entity in E 1 is associated with exactly one entity in E 2. (B) Some entity in E 1 is associated with more than one entity in E 2. (C) Every entity in E 2 is associated with exactly one entity in E 1. (D) Every entity in E 2 is associated with at most one entity in E 1.
Chapter 2
Structured Query Language
Relational algebra Select operator Project operator Set operators Union compatible relations Union operation Aggregate operators
Correlated nested queries Relational calculus Tuple relational calculus Tuple relational calculus DML Super key SQL commands
LEARNING OBJECTIVES
RelaTional algeBRa
- A set of operators (unary or binary) that take relation instances as arguments and return new relations.
- Gives a procedural method of specifying a retrieval query
- Forms the core component of a relational query engine
- SQL queries are internally translated into RA expressions
- Provides a framework for query optimization
SQL query
Relational algebra expression
Query expression plan
Executable code
Figure 1 Role of relational algebra in DBMS:
Relational Operations
A collection of simple ‘low-level’ operations used to manipulate relations.
- It provides a procedural way to query a database.
- Input is one (or) more relations.
- Output is one relation.
Relational operations
Unary operators Binary operators
- Select 1. Union
- Project 2. Intersection
- Difference
- Join
- Divide
- Cartesian product
Select Operator ( s )
Select operator is an unary operator. It can be used to select those tuples of a relation that satisfy a given condition. Notation: s (^) θ ( r ) s : Select operator(read as sigma) θ : Selection condition r : Relation name Result is a relation with the same scheme as r consisting of the tuples in r that satisfy condition θ Syntax : scondition (relation) Example: Table 2.1 Person Id Name Address Hobby 112 John 12, SP Road Stamp collection 113 John 12, SP Road Coin collection 114 Mary 16, SP Road Painting 115 Brat 18, GP Road Stamp collection