IT2301 Database Management Systems dbmsict'cmb'ac'lk

1
PREPARING FOR THE BIT
IT2301Database Management Systemsdbms_at_ict.cmb.ac
.lk
Preparing for BIT 09/08/2001
2
Announcements
Instructions for providing answersUse black or
blue ink ball point pens OR. Use a good quality
HB2 pencil.
3
Announcements
Exam Admission Cards and Model Papers were
posted.Few Corrections were made to some model
papers and the corrections published on the
web.There were OS questions using C, these would
be changed to Java.Students need not know C as a
programming language for this module
4
IT2301Model Question PaperLast Time (Q)
9-18Today (Q) 19-25
5
19) Which of the following characteristic(s) is
(are) not suitable for use as the primary key of
an employee? (a) Employee identity no. (b) Date
of birth (c) Address of employee (d) Full name
of employee (e) Photograph of employee
6
Primary Key
Uniqueness property, Fixed Length, Small in size
if used to link tables (a) Employee identity
no. (b) Date of birth (c) Address of
employee (d) Full name of employee (e) Photograp
h of employee
7
20) Which of the following statement(s) is (are)
true with respect to data types? (a) FLOAT data
type can store twice as many digits as
SMALLFLOAT. (b) INTEGER data type can store
decimal numbers exceeding that number
32,767 (c) VARCHAR data type often reduces disk
storage wastage when compared to CHAR data
type. (d) BLOB data is streams of bytes of fixed
length. (e) BYTE data type cannot store any type
of binary data.
8
Data Types
(a) FLOAT data type can store twice as many
digits as SMALLFLOAT. Refers to Precision and
Not Large in Number FLOAT (DOUBLE PRECISION)
Double Precision 14-16 significant
digits SMALLFLOAT (REAL) Single Precision 7-8
significant digits
9
Data Types
(b) INTEGER data type can store decimal numbers
exceeding that number 32,767 INTEGER can store
whole numbers Not Decimals
10
Data Types
(c) VARCHAR data type often reduces disk storage
wastage when compared to CHAR data type. CHAR
fixed length of specified value VARCHAR variable
length up to specified maximum
11
Data Types
(d) BLOB data is streams of bytes of fixed
length. Binary Large Object (BLOB) is a streams
of bytes of arbitrary value and length. A BLOB
might be a digitised image or sound
12
Data Types
(e) BYTE data type cannot store any type of
binary data. BYTE is a type of BLOB Stores
non-character-based information such as digitised
images, sound Therefore, can store binary data.
13
21) Which of the following may happen during the
process of normalization? (a) creation of new
tables, (b) splitting of a table into several
tables, (c) merging of several tables into one
table, (d) removing some relationships among
existing relations of the scheme, (e) removing
existing constraints.
14
Normalisation
(a) creation of new tables functional
dependencies on part of key and transitive
dependencies are removed by putting these
attributes in separate tables. Thus we create new
tables. As well as split existing tables.
(b) splitting of a table into several tables
15
Normalisation
(c) merging of several tables into one
table merging of tables is required only to
denormalised relations (d) removing some
relationships among existing relations of the
scheme, (e) removing existing constraints last
two options has nothing to do with normalisation
process.
16
22) Which set of operators represent the complete
set for relational algebra? (a) Union,
Difference, Projection, Selection, Cartesian
Product (b) Selection, Cartesian Product,
Projection, Union, Intersection (c) Cartesian
Product, Projection, Selection, Difference,
Intersection (d) Join, Union, Projection,
Selection, Difference (e) Difference, Projection,
Selection, Join, Intersection
17
Relational Algebra
Set Operators Union, Difference (Minus),
Intersection Relational Operators Projection,
Selection (Restrict), Cartesian Product, Join,
Natural Join, Divide The complete set is
sufficient to reconstruct any other operator.
E.g. Intersection, Join etc may be reconstructed
using this set. (a) Union, Difference,
Projection, Selection, Cartesian Product
18
23) Which of the following are supported by
Entry-level SQL-92 standard? (a) Embedded
SQL (b) Integrity enhancement (c) System
catalogs (d) Triggers and stored
procedures (e) Outer Join
19
Entry Level SQL-92

• SQL Standards knowledge of features supported at
  various levels is required
• The SQL92 (also called SQL2) standard has three
  levels of definition with a long list of
  features.
• Entry level SQL92
• Intermediate level SQL92
• Full SQL92

20
Full SQL89

• SQL89 is a revision of the original 1986
  standard. The full SQL89 standard includes an
  integrity enhancement which includes
• Primary key specification
• Referential integrity constraints (foreign key
  referencing a primary key)
• Default value for a column
• Check constraints (validation options for a
  column)

21
Entry Level SQL92

• Entry level SQL92 includes
• All of SQL89 plus the integrity enhancement
• Embedded SQL
• (a) Embedded SQL
• (b) Integrity enhancement

22
Intermediate Level SQL92

• Intermediate level SQL92 includes
• Entry level SQL92, plus
• Dynamic SQL
• Drop and alter table statements
• System catalog (c)
• Date/time data types
• Referential actions -- cascade delete
• Outer join operator (e)

23
Full Level SQL92

• Full level SQL92 includes
• Intermediate level SQL92, plus
• Bit string (BLOB) data type
• Referential actions -- cascade update

24
SQL3

• SQL3 includes
• Full level SQL92, plus
• Triggers and stored procedures (d)
• Object-oriented capabilities such as user defined
  data types and class hierarchies

25
24) Which set of key words cannot be used with
SQL INSERT statement to enter data? (a) Insert,
Select, Into, Set, Where (b) Insert, Into,
Values, Copy (c) Select, Into, From, Where,
Having (d) Into, Insert, Values, Null (e) From,
Distinct, To, Insert, Having
26
SQL INSERT
INSERT INTO table-name (column-name,),
VALUES (constant, NULL,) or SELECT
retrieval condition (a) Insert, Select, Into,
Set, Where (b) Insert, Into, Values,
Copy (c) Select, Into, From, Where,
Having (d) Into, Insert, Values, Null (e) From,
Distinct, To, Insert, Having
27
25) Select the statement(s) that is (are) correct
with respect to the use of JOIN (a) A table can
join to itself creating a self-referencing join
called a SELF-JOIN. (b) An EQUI-JOIN between two
tables in a SELECT statement returns only the
rows that have matching values in the join
columns. (c) In an OUTER-JOIN, all of the rows
of the dominant table are retrieved regardless of
whether there are corresponding rows in the other
table. (d) In an OUTER-JOIN, a join condition
does not need to have the WHERE
clause. (e) OUTER-JOIN cannot be used to join 3
tables.
28
JOIN
(a) A table can join to itself creating a
self-referencing join called a SELF-JOIN. Employe
e(empno, name, supervisor, deptno) SELECT e.empno,
e.name, s.name FROM Employee e, Employee
s WHERE e.empnos.supervisor
29
JOIN
(b) An EQUI-JOIN between two tables in a SELECT
statement returns only the rows that have
matching values in the join columns. Employee(emp
no, name, supervisor, deptno) Department(deptno,
name) SELECT e.empno, e.name, d.name FROM
Employee e, Department d WHERE e.deptnod.deptno
30
JOIN
(c) In an OUTER-JOIN, all of the rows of the
dominant table are retrieved regardless of
whether there are corresponding rows in the other
table. SELECT e.empno, e.name, d.name FROM
Employee e, OUTER JOIN Department d WHERE
e.deptnod.deptno Retrieve all employee data
regardless of match. Use NULL for those not
matched.
31
JOIN
(d) In an OUTER-JOIN, a join condition does not
need to have the WHERE clause. SELECT e.empno,
e.name, d.name FROM Employee e, OUTER JOIN
Department d WHERE e.deptnod.deptno OR SELECT
e.empno, e.name, d.name FROM (Employee AS e OUTER
JOIN Department AS d ON e.deptnod.deptno)
32
JOIN
(e) OUTER-JOIN cannot be used to join 3 tables.
33
Contact

• External Degree Unit (EDU) of the Institute of
  Computer Technology
• No. 221/2A, Dharmapala Mawatha,
• Colombo 7.
• Phone [phone]
• Fax [phone]
• Email bit_at_ict.cmb.ac.lk
• http//www.ict.cmb.ac.lk/bit.htm