Database Management System (DBMS) Faqs

MS Access Objective Questions

2008-11-20T02:03:00.000-08:00

Access	Oracle follows only seven Codd's rule.	True	False			A
Access	Access is a true RDBMS.	True	False			B
Access	In a table records are stored in rows and fields in columns.	True	False			A
Access	Text type field stores maximum 256 characters.	True	False			B
Access	Memo field stores 64,000 characters.	True	False			A
Access	In Yes / No field -1 stands no and 0 stands for yes.	True	False			B
Access	The lostfocus event occurs when a form or control looses the focus.	True	False			A
Access	We can not set a background picture in a report.	True	False			B
Access	We can see the report preview from tools menu à layout preview.	True	False			B
Access	Report header / footer sections does not come normally.	True	False			A
Access	We can have subform within a subform.	True	False			A
Access	We can type new values in a list box.	True	False			B
Access	When referential integrity is enforced, you can't enter a value in the foreign key field of the related table that doesn't exist in the primary key of the primary table.	True	False			A
Access	When the cascade delete related records check box is set, deleting a record in the primary table deletes any related records in the related table.	True	False			A
Access	If you want to see the SQL code that is generated by the query select Tools à SQL view from the menu.	True	False			B
Access	If you give a format property and an input mask the input mask gets precedence.	True	False			B
Access	A primary key is a must in a table.	True	False			B
Access	Double data type takes 4 bytes of space.	True	False			B
Access	There is one-to-many relationship between mainform and subform.	True	False			A
Access	You can not specify default value for a field.	True	False			B
Access	Foreign key must be primary key of another table.	True	False			A
Access	If required field property is turned on the user must enter something in this field in order to save the data.	True	False			A
Access	In access you can copy the only structure of the table without the records.	True	False			A
Access	A macro can be run automatically when a database opens.	True	False			A
Access	A report can only be based on table.	True	False			B
Access	In access page footer comes before report footer in all pages.	True	False			B
Access	Currency is not a valid data type in access.	True	False			B
Access	In append query the table where the data will be appended should have the same structure and primary key.	True	False			A
Access	It is possible to enter a Null value in the foreign key.	True	False			A
Access	It is not possible to set two fields of a table as primary key.	True	False			B
Access	In access you can store the information of a video files in table.	True	False			A
Access	In access, sorting the records in a table changes the order in which the records are displayed.	True	False			A
Access	In access after you select all the fields necessary for the query, access builds the SQL in the background.	True	False			A
Access	To run a macro automatically, the name of the macro is	AUTOEXEC	AUTOEXEC.BAT	AUTO	None of the above	A
Access	In Validation rule <>0 means	entry is not null	entry must be a nonzero value	none of the above		B
Access	In input masks (999) 999-9999 means	(206) 555-0248	( ) 555-0248	both of the above	none of the above	C
Access	You can copy records from one table to another table in the current database or another database.	Update query	Append query	Maketable query		B
Access	To delete all orders placed in 1990 we have to use	Update query	Append query	Maketable query	Delete query	D
Access	To increase salary by 5% we have to use	Update query	Append query	Delete query		A
Access	In access relationships are	one-to-one	one-to-many	both of the above		C
Access	Access lets you set up following kinds of joins -	Only Inner join	Only outer join	Only self join	None of the above	D
Access	Extension of an access database is	.dbf	.mdb	.doc	.ppt	B
Access	It allows you to set the message that appears if the validation rule fails.	validation rule	validation text	default value	all of the above	B
Access	Primary key field must be	not null and unique	null and unique	not null and indexed		A
Access	You can store picture in a field of data type	picture	OLE Object	None of the above	a & b	B
Access	An example of calculated query is	label control	text control	both a & b		C
Access	Line control is a example of	bound control	unbound control	calculated control		B
Access	There is one-to-many relationship between mainform and subform.	True	False			A
Access	Access support full RDBMS concept.	True	False			B
Access	Using multiple row , we can not create primary key.	True	False			B

SQL - Structured query language notes

2008-08-12T02:42:00.000-07:00

Data Types

Binary(n)	It is used to store Hexadecimal values with a maximum length of 8000 bytes. It is used when data entries in a column are expected to have consistent size.
Varbinary(n)	It is used to store Hexadecimal values with a maximum length of 8000 bytes. It is used when data entries in a column are expected to vary in size.
Image	0 to 2³¹-1 bytes.
Char(n)	Use char when every entry for a column has the same fixed length. Maximum upto 8 KB or 0-8000 bytes
Varchar(n)	Use char when every entry for a column has varied length. Maximum upto 8 KB or 0-8000 bytes
Nchar(n)	Use char when every entry for a column has the same fixed length. Maximum upto 4 KB or 0-4000 bytes
Nvarchar(n)	Use char when every entry for a column has varied length. Maximum upto 4 KB or 0-4000 bytes
Text	Used to store large amount of data with a max limit of 2147483647 chars.
Ntext	Storage size is 2 times the number of chars entered
Datetime	8 bytes. Date between 1-1-1753 to 31-12-9999
Smalldatetime	4 bytes. Date between 1-1-1900 to 6-6-2079
Int	4 bytes. Between -2³¹ to 2³¹
Smallint	2 bytes. Between -2¹⁵ to 2¹⁵
Tinyint	1 byte. Between 0 to 255
Bigint	8 bytes. Between -2⁶³to 2⁶³
Money	8 bytes. Between -2⁶³to 2⁶³
smallmoney	4 bytes. Between -214748 to 214748
Bit	Integer data with a value of 0 and 1. 0-8 then 1 byte. 9-16 then 2 bytes.

JOINS

Joins are used to retrive data from two or more tables based on a logical relationship between tables. It specifies foreign key relationship between the tables.

Types Of JOINS

Inner
Outer

Ø Left Outer

Ø Right Outer

Self

Inner Join

Select B.Branch_code,B.add1,R.tno,R.Pass_name

from Branch as B

INNER JOIN

Reservation as R ON

B.Branch_code=R.Branch_code

Order by B.Branch_code

Left Outer Join

Select R.Flightno,R.Branch_code,B.city,R.Class from

Reservation as R

LEFT OUTER JOIN

Branch as B ON

B.Branch_code=R.Branch_code

Order by R.flightno

Right Outer Join

Select B.Branch_code, B.city, R.flightno, R.class from

Reservation as R

RIGHT OUTER JOIN

Branch B ON

R.Branch_code=b.Branch_code

Self Join

Select A.origin+A.destination+B.destination from

Fare as A, Fare as B where

A.origin=b.destination

Data Integrity

Data Integrity means reliability and accuracy of data.

Types Of Data Integrity

1. Entity Integrity

Domain Integrity
Referential Integrity
User-Defined Integrity

Entity Integrity

Entity integrity defines row as a unique entity. It is also called as Row Integrity.

Domain Integrity

Domain Integrity enforce restrictions on the values entered.

Referential Integrity

This integrity is used for creating relationships between the tables.

Entity Integrity	Primary Key Unique key Constraint IDENTITY
Domain Integrity	DEFAULT Constraint Foreign Key Check Constraint NOT NULL
Referential Integrity	Foreign Key Check Constraint
User Defined Integrity	Rules Stored Procedures Triggers

Constraint

Constraint is a property that can be placed on a column or set of columns in a table.

Rules

Rules provide a defined restriction on the values, for a column or a user-defined datatype. It can be used for insert and update statement.

Primary Key

A primary key is a column or group of columns that uniquely identify rows in a table. It does not contain null.

Foreign Key

A foreign key column refers to the values of primary key column of another table or same table.

Unique Key

The Unique constraint is similar to the primary key constraint except that it allows null values.

Check Constraint

It is used to enforce domain integrity.

Set Default Property

ALTER TABLE SERVICE add CONSTRAINT

Serv_def DEFAULT 100 for SS_Fare

Sp_unbindefault ‘service.ss_Fare’

Alter table Service drop constraint Serv_def

Set Identity Property

ALTER TABLE Cancellation add

Srlno int IDENTITY(2000,1)

Set Primary Key

ALTER TABLE Branch

ALTER COLUMN Branch_code char(4) NOT NULL

ALTER TABLE Branch

Add Constraint Branch_const Primary Key(Branch_code)

CREATE TABLE A(B int Primary Key)

Set Foreign Key

ALTER TABLE FlightSchedule

Add Constraint Flt_const FOREIGN KEY(Airbusno)

REFERENCES Airbus(Airbusno)

ALTER TABLE FlightSchedule

NoCheck Constraint Flt_const

Set Unique Key

ALTER TABLE FlightSchedule

Add Constraint fltsch_const

UNIQUE(Flightno)

ALTER TABLE FlightSchedule

DROP Constraint fltsch_const

Set Check Constraint

ALTER TABLE Reservation

Add Constraint res_chkconst

Check(class in(‘B’,’E’,’F’))

Set Rules

CREATE RULE Rule2 as

@Serv_code in(‘cc’,’nu’,’wc’)

Sp_bindrule Rule2, ‘Reservation.SS_code’,FUTUREONLY

Indexes

Types Of Indexes

Clustered Index

A clustered index determines the storage order of data in a table.

A table can have only one clustered index.

Non-Clustered Index

A non-clustered index specifies a logical ordering only. Here the data is stored in one place, the index in the another, with pointers to the storage location of the data.

Unique Index

A unique index ensures that the indexed column contains no duplicate values.

Composite Index

A composite index consists of 2 or more columns indexed together. The maximum number of columns that can be combined is 16 and the allowable size is 900 bytes.

Clustered Index

CREATE CLUSTERED INDEX fare_route

On Fare(Route_code)

Non-Clustered Index

CREATE NONCLUSTERED INDEX

fare_desc on fare(route_desc)

Unique Index

CREATE UNIQUE INDEX branch_city

On Branch(city)

Composite Index

CREATE INDEX comp_index on

reservation(TNO,flightno)

Drop Index

drop index fare.fare_route

Views

A view is an alternate way of looking at data from one or more tables in the database. A view is a virtual table, usually created as a subset of columns or rows from one or more tables. However, a view does not exist as a set of stored data values in a database. The rows and columns come from tables referenced in the query. The data will be displayed directly from the table at the time of execution. A view acts as a filter on the table addressed in the query.

Advantages

For the end user

o Easier to understand results

o Easier to obtain data

For the developer

o Easier to restrict data retrieval

o Applications are easy to maintain

Guidelines for creating view

View can be created only in the current database
A view name should be similar to the table name because it is easy to remember and understand
A view can be built on other views. SQL Server allows views to be nested upto
32 levels.
Defaults, Rules and triggers cannot be associated with views.
Views cannot be indexed.
Temporary tables cannot participate in views
The query defining the view cannot include ORDER BY,COMPUTE, COMPUTE BY clauses or the INTO Keyword

Modifying data through views

1. No aggregate functions can be used in the select list.

2. The view contains at least one table in the from clause.

3. The view has no derived columns in the select list.

Indexed Views

Indexed view has been introduced with SQL Server 2000 and is supported only by its enterprise edition.

Create View cv with schemabinding as

Select B.Branch_code,B.add1,R.tno,R.Pass_name

from [dv6-0205b].Branch as B

INNER JOIN

[dv6-0205b].Reservation as R ON

B.Branch_code=R.Branch_code

You have to enable the ARITHABORT option while creating the index. The ARITHABORT option is used to terminate the query in case of any overflow or divide by zero error. This option helps to discontinue arithmetic operations in case of errors.

SET ARITHABORT ON

CREATE UNIQUE CLUSTERED INDEX IND

ON cv(BRANCH_CODE)

Distributed partitioned views

SQL Server 2000 supports the feature “Distributed partitioned views(DPV)”. This feature allows partitioning and distributing data horizontally to multiple number of servers and databases. It also makes us feel that it is uniformaly one database.

Partitioned tables are distributed across multiple servers. Hence, each server needs to access every other server. Therefore, you need to configure all the servers as linked servers.

create view partview as

Select * from server2k.pubs.dbo.stores

UNION ALL

Select * from aceserver.pubs.dbo.stores

STORED PROCEDURES

Variables

Variables are defined memory locations, which are given a name and which contain some value. Since variables are created in memory, whatever they contain is not permanent. Variables are mainly used for calculations or to store some value for stored procedures. A variable is an object that can hold a data value.

There are two types of variables :

Local Variables

System Global Variables

Ex:

Declaring Variables

DECLARE @FNO CHAR(4)

DECLARE @SNO INT

Set value to variables

SET @FNO=’abc’

SET @TNO=1

Using a variable in an sql query

Select * from flight where flightno=@FNO

Select * from reservation where tno=@TNO

System Global Variables

System Global Variables	This Variable returns
@@Trancount	Transactions currently open on the connection
@@Servername	Local server name
@@RowCount	Numbers of rows effected by the latest SQL statement
@@Nestlevel	Nesting level of the current stored procedure
@@Language	Language name
@@Servicename	SQL Server service name on the current computer
@@Procid	ID of the current stored procedure
@@Connections	Number of connections established with the server since it is started
@@Fetch_Status	Status of the FETCH operation. Returns 0 if it is successful, -1-2 if it is unsuccessful.

Stored Procedures

Stored procedures are vital tool for any database system. Stored procedures are written by database developers or database administrators to run commonly performed administrative tasks or to apply complex business rules. The stored procedure contains data manipulation or data retrieval statements.

Definition

A stored procedure is a precompiled collection of Transact-SQL statements stored with a name and processed as a unit. SQL Server provides some precompiled stored procedures for managing SQL Server and displaying information about the databases and users. These stored procedures are called system stored procedures.

Benefits of stored procedures

Speed

Faster access to data

Less compilation time

Modular programming

Consistency

Enhance security mechanism

Types of stored procedures

System stored procedures
Extended stored procedures.
User-defined stored procedures

System stored procedures

System stored procedures are supplied by SQL Server, they are precompiled collections of Transact-SQL statements. System stored procedures are provided as shortcuts for retrieving information from system tables.

The name of all system procedures starts with ‘sp_’.

System stored procedures are located in the master database and are owned by the system administrator.

System stored procedures are grouped into the following categories:

1. Catalog procedures

2. Security procedures

3. System procedures

4. Cursor procedures

5. Distributed query procedures

6. SQL Server Query Agent procedures

7. Sql Mail extended procedures

Extended stored procedures

Extended stored procedures are similar to user-defined and system stored procedures except that they are not residents of SQL Server. They work outside SQL Server and are stored as DLLs. SQL Server dynamically locates them and executes them. Extended stored procedures are usually denoted by the ‘xp_’ prefix.

Dynamic link library is an executable routine containing a specific set of functions stored in .dll file and loaded on demand when needed by the program.

User defined stored procedures

Apart from using the built-in stored procedures, you can also create your own stored procedures.

To create stored procedures, CREATE PROCEDURE or CREATE PROC statements are used. All stored procedures are created in the current database. In order to create a procedure you must have the permission to execute the CREATE PROCEDURE statement. This permission is assigned by default to the database owner but the owner can transfer it to the other users.

The syntax is:

CREATE PROC[EDURE] procedure_name

Creating procedures

create procedure abc as

Select B.Branch_code,B.add1,R.tno,R.Pass_name

from Branch as B

INNER JOIN

Reservation as R ON

B.Branch_code=R.Branch_code

Order by B.Branch_code

Exec abc

Using parameters

create procedure abc

@tno int

Select B.Branch_code,B.add1,R.tno,R.Pass_name

from Branch as B

INNER JOIN

Reservation as R ON

B.Branch_code=R.Branch_code

where tno=@tno

Order by B.Branch_code

EXEC abc 10012

Using return keyword

create proc p1 with recompile

declare @sal int

Select @sal=first_fare from fare where origin='new york'

return @sal

drop proc p1

declare @a int

exec @a=p1

Select @a

IF ELSE Statement

Ex:1

Select * from paemployee

if (select count(*) from paemployee where deptid='d001')>0

print 'employee exists'

else

print 'employee does not exists'

Ex:2

declare @mvar int

select @mvar=(select count(*) from paemployee where deptid='d001')

print @mvar

if @mvar>0

print 'employee exists'

else

print 'employee does not exists'

Ex : 3

if exists(Select count(*) from paemployee where deptid='d001')

print 'employee exists'

else

print 'employee does not exists'

BEGIN END

Ex:1

declare @cnt int

if (select count(*) from paemployee where deptid='d001')>0

begin

select @cnt=count(*) from paemployee where deptid='d001'

print convert(varchar(12),@cnt)+' Employee exists'

end

else

print 'sorry no employee'

SELECT * FROM paEMPloyee WHERE deptid='d001'

WHILE LOOP

Ex : 1

declare @cnt int

set @cnt =1

while

@cnt <=10

begin

print @cnt

set @cnt= @cnt+1

end

Triggers

Triggers are special stored procedures created by the user and provoked by SQL Server when data modification statements are issued. Triggers are special objects created on the table and are part of the database.

A trigger is invoked automatically whenever the data in the table is modified. They are invoked in response to INSERT, UPDATE, or DELETE Transact SQL Statements.

Creating a trigger

Syntax:

CREATE TRIGGER Trigger_name

On table

FOR {[DELETE] [,] [INSERT] [,] [UPDATE] }

[WITH ENCRYPTION]

AS Sql_statement

Examples

1. TRIGGER FOR INSERT

CREATE TRIGGER INS_TRIG

ON RESERVATION

FOR INSERT

IF((SELECT FLIGHT_DATE FROM INSERTED)

BEGIN

PRINT 'ERROR'

ROLLBACK TRANSACTION

END

SELECT * FROM RESERVATION

INSERT INTO RESERVATION(TNO,FLIGHT_DATE) VALUES(1,'12/12/1999')

2. TRIGGER FOR DELETE

CREATE TRIGGER DEL_TRIG

ON BRANCH

FOR DELETE

IF(SELECT COUNT(*) FROM DELETED)>2

BEGIN

PRINT 'ERROR'

ROLLBACK TRANSACTION

END

DELETE FROM BRANCH

3. TRIGGER FOR TABLE LEVEL UPDATE

CREATE TRIGGER TLT

ON FARE

FOR UPDATE

IF(SELECT FIRST_FARE FROM INSERTED)<2000

BEGIN

PRINT 'THE FARE CANNOT BE LESS THAN 2000'

ROLLBACK TRANSACTION

END

SELECT * FROM FARE

UPDATE FARE SET FIRST_FARE=100 WHERE ORIGIN='NEW YORK' AND DESTINATION='AMSTERDAM'

4. TRIGGER FOR FIELD LEVEL UPDATE

CREATE TRIGGER FLT

FARE

FOR UPDATE

IF(UPDATE(FIRST_FARE))

BEGIN

PRINT 'YOU CANNOT UPDATE FARE'

ROLLBACK TRANSACTION

END

UPDATE FARE SET FIRST_FARE=11100 WHERE ORIGIN='NEW YORK' AND DESTINATION='AMSTERDAM'

5. TRIGGER WITH ENCRYPTION

CREATE TRIGGER ENT

ON FLIGHT

WITH ENCRYPTION

FOR INSERT,UPDATE

IF(SELECT Flightdate FROM INSERTED)

BEGIN

PRINT 'THE FLIGHT DATE CANNOT BE LESS THAN TODAYS DATE'

ROLLBACK TRANSACTION

END

UPDATE FLIGHT SET FLIGHTDATE='1990-09-01' WHERE FLIGHTNO='NL35'

SP_HELPTEXT ENT

SP_HELPTRIGGER FARE

6. ENFORCING REFERENTIAL INTIGRITY USING TRIGGER

CREATE TRIGGER ERIT

ON RESERVATION

FOR INSERT,UPDATE

IF((SELECT SS_CODE FROM INSERTED) NOT IN('CC','NU','WC'))

BEGIN

PRINT 'ERROR'

ROLLBACK TRANSACTION

END

UPDATE RESERVATION SET SS_CODE='NA' WHERE TNO=10012

7. CASCADING TRIGGER

SELECT * FROM FLIGHTSCHEDULE

SELECT * FROM AIRBUS

CREATE TRIGGER CASCTRIG

ON AIRBUS

FOR DELETE

DELETE AIRBUS FROM DELETED,FLIGHTSCHEDULE

WHERE FLIGHTSCHEDULE.AIRBUSNO =DELETED.AIRBUSNO

DELETE FROM AIRBUS WHERE AIRBUSNO='AB04'

8. INSTEAD OF TRIGGER

CREATE TRIGGER INSTR

ON SERVICE INSTEAD OF INSERT

BEGIN

SELECT SS_CODE FROM INSERTED

SELECT SS_CODE FROM DELETED

SELECT SS_CODE FROM SERVICE

END

INSERT INTO SERVICE VALUES ('SS','SDSD',2434)

DROP TRIGGER INSTR

Locks

Lock is a restriction on access to data in multi user environment. SQL Server locks users out of a specific record, field or file automatically to maintain security or prevent concurrent data manipulation problems.

Sql server uses locking to ensure transactional integrity and database consistency. Locking prevents users from reading the data being changed by other users.It prevents multiple users from changing the same data at the same time.

Type of Locks

There are three types of locks :

1. Shared Locks

Shared locks are used for the operations that do not change or update the data such as Select statement. It is used for read only operations. Shared locks allow concurrent transaction to read(Select) a resource. No other transaction can modify the data while shared locks exist on the resource. Shared locks are released as soon as the data has been read.

2. Exclusive Locks

Exclusive locks are used for data modification operations such as update, delete, or insert. Exclusive locks ensures that multiple updates cannot be made to the same resource at the same time. No other transaction can read or modify data locked with an exclusive lock.

3. Update Locks

Update locks are used for update transactions. When data is expected it is first locked using a shared lock. Once the data is found, the shared lock is upgraded to an exclusive lock to modify the data. The problem can arise when one of two sessions, having shared locks on the same resource, wants to change to an exclusive lock to modify the data, but cannot do that because the exclusive lock is not compatible with the shared lock. To avoid this deadlock problem, update locks are used. Only one session can obtain an update lock to a resource at a time.

Permissions

GRANT CREATE DATABASE, CREATE TABLE

TO Mary, John

GRANT INSERT, UPDATE, DELETE

ON authors

TO Mary, John, Tom

DENY SELECT, INSERT, UPDATE, DELETE

ON authors

TO Mary, John, Tom

Transactions

1. EXPLICIT TRANSACTION

Explicit transactions are manually configured transactions in which the beginning and the end of transaction are clearly defined.

BEGIN TRANSACTION

insert into a values(3,2)

create table a(b int,c int)

insert into a values(1,2)

COMMIT TRANSACTION

Select * from a

2. IMPLICIT TRANSACTION

Implicit transaction is enabled with the statement SET IMPLICIT_TRANSACTION ON. The user starts the Explicit Transaction whereas server starts the implicit transaction. Once the implicit transaction is set on, SQL Server will generate a continuous chain of implicit transactions until implicit transaction is set off. If any transaction in the chain of implicit transaction fails, the entire chain is aborted and all successful transactions are rolled back to their original state. This type of transaction does not need BEGIN TRANSACTION. Implicit transaction runs automatically.

SET IMPLICIT_TRANSACTIONS ON

INSERT INTO A VALUES(2,3)

INSERT INTO A VALUES(3,4)

SELECT COUNT(*) FROM A

COMMIT TRANSACTION

INSERT INTO A VALUES(2)

COMMIT TRANSACTION

SET IMPLICIT_TRANSACTIONS OFF

3. Save Point for a transaction

begin transaction

create table ab(a int, b int)

save tran t1

insert into ab values(10,20)

save tran t2

insert into ab values(30,40)

save tran t3

rollback tran t2

end transaction

CURSORS

A cursor is a database object used by applications to manipulate the data by rows instead of sets.

declare abc cursor

scroll

for

select * from memp

open abc

fetch abc

fetch next from abc

fetch prior from abc

fetch first from abc

fetch last from abc

fetch absolute 2 from abc

fetch relative -1 from abc

Types of Cursors

LOCAL

Specifies the scope of the cursor that is confined to the stored procedure or trigger in which it is created.

GLOBAL

Specifies that the scope of the cursor is global.

FORWARD_ONLY

Specifies that the cursor can only be scrolled from first to the last row. fetch next is only supported.

SCROLL

Specifies that all the fetch options are available.

STATIC

Define a cursor that makes a temporary copy of the data to be used by the cursor. No modifications

KEYSET

Specifies the order of the rows in the cursor is fixed when the cursor was opened.

DYNAMIC

Defines a cursor that reflects all the changes made to the rows in the result set as one scrolls around the cursor. Does not support FETCH ABSOLUTE

FAST_FORWARD

Specifies a FORWARD_ONLY and READ_ONLY cursor. FAST_FORWARD and FORWARD_ONLY cursors are mutually exclusive, if one is specified the other one cannot be specified

READ_ONLY

Prevents updates made through this cursor.

SCROLL_LOCKS

Specifies that the positioned updates or deletes made through the cursor are guaranteed to succeed. FAST_FORWARD cursors cannot be specified along with SCROLL_LOCKS.

OPTIMISTIC

Specifies that the positioned updates or deletes made through the cursor do not succeed, if the row is updated since it was read into the cursor.

Examples

1. SCROLL

DECLARE CUR1 CURSOR

SCROLL

FOR SELECT * FROM memp

open cur1

fetch cur1

fetch next from cur1

fetch prior from cur1

fetch first from cur1

fetch last from cur1

fetch absolute 3 from cur1

fetch relative 3 from cur1

close cur1

Select @@fetch_status

WHILE @@FETCH_STATUS=0

BEGIN

FETCH NEXT FROM CUR1

END

2. FORWARD_ONLY

DECLARE CUR2 CURSOR

FORWARD_ONLY

FOR

SELECT * FROM Memp

FOR UPDATE

CLOSE CUR2

OPEN CUR2

FETCH CUR2

UPDATE memp SET desg='Manager' WHERE CURRENT OF CUR2

DEALLOCATE CUR2

FETCH PRIOR FROM CUR2

3. KEYSET

DECLARE KEY_CUR CURSOR

KEYSET

FOR

SELECT * FROM memp

OPEN KEY_CUR

FETCH KEY_CUR

WHILE @@FETCH_STATUS=0

BEGIN

FETCH NEXT FROM KEY_CUR

END

4. DYNAMIC

DECLARE DYNA CURSOR

DYNAMIC

FOR

SELECT * FROM memp WHERE deptid='d001' FOR UPDATE

OPEN DYNA

FETCH DYNA

CLOSE DYNA

5. READ_ONLY

DECLARE READ_CUR CURSOR

READ_ONLY

FOR SELECT * FROM memp

OPEN READ_CUR

FETCH READ_CUR

DELETE FROM memp WHERE CURRENT OF READ_CUR

6. SCROLL_LOCKS

DECLARE SCRL CURSOR

SCROLL_LOCKS

FOR

SELECT * FROM emp FOR UPDATE

OPEN SCRL

FETCH NEXT FROM SCRL

UPDATE emp SET salary=4500 WHERE CURRENT OF SCRL

WHILE @@FETCH_STATUS=0

BEGIN

FETCH NEXT FROM SCRL

END

7. OPTIMISTIC

DECLARE OPTIS1 CURSOR

SCROLL

OPTIMISTIC

FOR

SELECT TNO FROM RESERVATION

FOR UPDATE

open optis1

fetch optis1

ER-model

2008-08-12T02:41:00.000-07:00

Participation Constraints

Total participation (indicated by double line): every entity in the entity set participates in at least one relationship in the relationship set

Ø E.g. participation of loan in borrower is total

Ø every loan must have a customer associated to it via borrower

Partial participation: some entities may not participate in any relationship in the relationship set

Weak Entities

An entity set that does not have a primary key is referred to as a weak entity set.
The existence of a weak entity set depends on the existence of a identifying entity set

Ø it must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set

The discriminator (or partial key) of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set.
The primary key of a weak entity set : Primary key of the strong entity set + the weak entity set’s discriminator.

Class Hierarchies

Specialization : Top-down design process; we designate subgroupings within an entity set that are distinctive from other entities in the set.
These sub groupings become lower-level entity sets that have attributes or participate in relationships that do not apply to the higher-level entity set.
Attribute inheritance – a lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked.
Generalization : A bottom-up design process – combine a number of entity sets that share the same features into a higher-level entity set.
Specialization and generalization are simple inversions of each other; they are represented in an E-R diagram in the same way.

Specialization

Specialization/Generalization

The ISA relationship also referred to as superclass – subclass relationship
Constraint on whether or not entities may belong to more than one lower-level entity set within a single generalization.

Ø Disjoint : an entity can belong to only one lower-level entity set. Noted in E-R diagram by writing disjoint next to the ISA triangle

Ø Overlapping : an entity can belong to more than one lower-level entity set

Completeness constraint -- specifies whether or not an entity in the higher-level entity set must belong to at least one of the lower-level entity sets within a generalization.

Ø Total : an entity must belong to one of the lower-level entity sets

Ø Partial: an entity need not belong to one of the lower-level entity sets

Aggregation

Suppose we want to record managers for tasks performed by an employee at a branch

Relationship sets works-on and manages represent overlapping information

Eliminate this redundancy via aggregation

Ø Treat relationship as an abstract entity

Ø Allows relationships between relationships

Ø Abstraction of relationship into new entity

• Without introducing redundancy, the following diagram represents:

Ø An employee works on a particular job at a particular branch

Ø An employee, branch, job combination may have an associated manager

E-R Design Decisions

The use of an attribute or entity set to represent an object.
Whether a real-world concept is best expressed by an entity set or a relationship set.
The use of a ternary relationship versus a pair of binary relationships.
The use of a strong or weak entity set.
The use of specialization/generalization – contributes to modularity in the design.
The use of aggregation – can treat the aggregate entity set as a single unit without concern for the details of its internal structure.

Entity Vs Attribute

Should ‘Address’ be an attribute of ‘Employees’ or an entity connected to ‘Employees’ through a relationship
Depends upon the usage of the data and the data semantics:

Ø If we have several addresses per employee, address must be an entity

Ø If the structure (city, state, etc) are important, address must be modeled as an entity

Entity Vs Relationship

The diagram is valid if the manager gets a separate discretionary budget for each dept.
What if the manager gets a discretionary budget that covers all managed departments?

Ø Redundancy : dbudget stored for each dept. manage by the manager

Ø Misleading : suggests that dbudget associated with dept-mgr combination

Binary Vs Ternary relationship

This ER-diagram models a situation in which an employee can own several policies, each policy can be owned by several Employees, and each dependent can be covered by several policies
Suppose we have the following additional constraints :

Ø A policy cannot be owned jointly by two or more employees

Ø Every policy must be owned by some employee

Ø Dependents is a weak entity set, and each dependent entity is uniquely identified by the combination pname+policyid

Aggregation Vs Ternary relationship

Introduction to DBMS

2008-08-12T01:38:00.000-07:00

Managing Data

Data is an Important organizational asset
Organizations require tools for effective management of their large and complex datasets
Badly managed data is a liability - the cost of acquiring & managing it far exceeding the value derived from it

What is DBMS?

A very large, integrated collection of data.
Models real-world enterprise.
Entities (e.g., students, courses)
Relationships (e.g., Madonna is taking CS564)
A Database Management System (DBMS) is a software package designed to assist in maintaining & utilizing large collections of data.

File System Vs DBMS

Application using files need to swap large datasets between main memory and secondary storage
Special programs would be needed to identify all data items
Special code for different queries
Multiple concurrent users could result in inconsistency
Crash recovery
Security and access control

Advantages of DBMS

Data Independence
Efficient data access
Data integrity and security
Data administration
Concurrent access and Crash recovery
Reduced application development time

Describing & storing data in DBMS

A DBMS allows a user to define the data to be stored in terms of a data model
A data model is a collection of high-level data description constructs that hide several lowlevel storage details
The relational model of data is the most widely used model today.

– Main concept: relation, basically a table with rows and columns.

A description and storage of data in terms of the data model is called a schema
In the relational model, the schema for a relation specifies its name, name & type of each field

– Students(sid: string, name: string, login: string,age: integer, gpa:real)

Data Independence

· Application programs are insulated from changes in the way the data is structured and stored

· Data independence is achieved through use of three levels of data abstraction

Levels of abstraction

The data in a DBMS is described at three levels of abstraction

Levels of abstraction

Views describe how users see the data.
Conceptual(logical) schema defines logical structure
Physical schema describes the files and indexes used.

Ø *Schemas are defined using DDL

Ø *Data is modified/queried using DML.

Conceptual Schema

· In relational DBMS, the conceptual schema describes all relations that are stored in the database

Ø Students(sid: string, name: string, login: string, age: integer, gpa:real)

Ø Courses(cid: string, cname:string, credits:integer)

Ø Enrolled(sid:string, cid:string, grade:string)

· These relations contain information about entities, such as Students & courses and about relationships, such as Enrollment

Physical Schema

· The physical schema summarizes how relations described in the conceptual schema are actually stored on secondary storage devices such as disks and tapes

· This schema decides :

Ø The file organizations to be used to store the relations

Ø Indexes needed to speed up data retrieval operations

External schema

· External schema allows data access to be customized at the level of individual users or group of users

· Any given database has exactly one conceptual & physical schemas but it may have a number of external schemas

· Each external schema consists of a collection of one or more Views and relations from the conceptual schema

Data Independence

Changes to Conceptual schema need not affect the users view of data - the definition
of the view relation can be modified so that users get the same view as before
Users can be shielded from changes in the logical structure of the data
Logical data independence: Protection from changes in logical structure of data.
The conceptual schema hides details such as how the data is actually laid out on the disk, the file structure, and the choice of indexes
Physical data independence: Protection from changes in physical structure of data.

Architecture of DBMS

People who work with databases

Database Implementors
End users
Database application developers
Database administrator(DBA)

DBA is responsible for:

Ø Design of the conceptual &physical schemas

Ø Security & authorization

Ø Data availability & recovery from failures

Ø Database tuning