EX.

NO:1 DATABASE DEVELOPMENT LIFE CYCLE: PROBLEM DEFINITION AND

REQUIREMENT ANALYSIS, SCOPE AND CONSTRAINTS

AIM

To study about database development life cycle and problem definition and requirement analysis
scope and constraints.

OVERVIEW OF DDM

Database management systems are generally categorized as transaction processing systems,

decision support systems and/or knowledge-based systems. During their development each of
these types of DDM introduces different problems and challenges. Traditionally, SDLC models
designed for developing DDM followed the design-first-implement-later approach because of the
DDM were mainly of the transaction processing type [Weitzel and Kerschberg, 1989]. The authors
believe, as we will explain later, that the design- first-implement-later approach does not work for
the databases underlying data mining or knowledge-base systems or for that matter for any system
where the requirements change very frequently.

Traditional Lifecycle Models

This section discusses the traditional lifecycle models and shows that, at least
one of the properties required for database system development (scope restriction, progressive
enhancement, incremental planning and pre-defined structure), is missing from each of these
lifecycles. For this reason, these life cycle models are not completely suitable for developing
database systems. In the remaining of this section we briefly describe some of the most popular
software models and point out their deficiencies for developing DBMSs. Some of the Database
software is Oracle RDBMS, IBM DB2, Microsoft SQL Server, MySQL, Microsoft Access,
FileMaker etc.

Waterfall model

This is the most common of all software models [Pressman, 2007]. The phases in the
waterfall cycle are: project planning, requirements definition, design, development, testing, and
installation and acceptance (See Figure 1). Each of these phases receives an input and produces an
output (that serves as the input for next phase) in the form of deliverables.
 The waterfall model has a documentation driven approach which, from the user’s point of
view, is considered one of its main weaknesses.The system specifications, which are finalized early
in the lifecycle, may be written in a non-familiar style or in a formal language that may be difficult
for the end user to understand [Schach, 2008]. Generally, the end user agrees to these specifications
without having a clear understanding of what the final product will be like. This leads to
misunderstood or missing requirements in the software requirements specification.

INTRODUCTION TO DATABASE DESIGN AND MANAGEMENT

Prototype model: In this life cycle model, the developers create a prototype of the
application based on a limited version of the user requirements [Pfleeger and Atlee, 2010]. The
prototype consists mainly of a “hallow graphics” which shows some basic and simple
functionality. However, this may create a problem because the user may view the prototype as
it were the final product overlooking some of the requirements specified in the SRS which may
not be met fully by this “final product” [Pfleeger and Atlee, 2010]
Spiral model: This model is a combination of the prototyping and waterfall model [Pfleeger and Atlee,
2010]. Starting with the requirements and a development plan, thesystem prototypes and the risks
involved in their developments are analyzed through aniterative process. During each iteration

alternative prototypes are considered based upon the documented constraints and risks of the
previous iteration.

Rapid application development model (RAD):

The basic approach of this model is to let the user try the application before it is finally
delivered. The users provide feedback based upon their hands-on experience with the system. The
foremost problem with this model is that it is very easy to get caught in an unending and
uncontrollable cycle of enhancements. This will lead to violations of the progressive enhancement
and scope restriction property.

Database Development Lifecycle

As we have shown in the previous paragraphs, each of the traditional lifecycle models is missing at
least one of the four properties required for database system development. In this section the authors
propose a new lifecycle model that is adapted from the traditional lifecycle and which is enhanced for
database system development.

After the previous phase has been completed it is necessary to analyze the data to consider issues of
extreme importance such as feasibility, cost, scope and boundaries, performance issues, security
issues, portability requirements, maintenance and the design model of the expected system. This
analysis of the requirements and organizational data helps to identify potential problems and constraints
that could arise during development phases. Once the aforementioned requirements and issues have
been thoroughly analyzed it is necessary to envision a timeline for future work database is implemented
using the physical design documents developed earlier during the design phase. The database
implementation and loading phase includes steps such as the follows: creating database tables, populating
the tables, building constraints and querying the data.
The Database Initial Study:

In the Database initial study, the designer must examine the current system’s operation within the
company and determine how and why the current system fails. The overall purpose of the database initial
study is to:
• Analyze the company situation.
• Define problems and constraints.
• Define objectives.
• Define scope and boundaries.
a. Analyze the Company Situation:
The company situation describes the general conditions in which a company operates, its
organizational structure, and its mission. To analyze the company situation, the database
designer must discover what the company’s operational components are, how they
function, and how they interact.

b. Define Problems and Constraints:

The designer has both formal and informal sources of information. The process of
defining problems might initially appear to be unstructured. Company end users are often
unable to describe precisely the larger scope of company operations or to identify the real
problems encountered during company operations.
 CONCLUSION
A complete and correct database system is difficult to create if the SDLC does not take into
account the intrinsic characteristics of the system to be developed and itself does not accommodate
properties like scope restriction, progressive enhancement, incremental planning and pre-defined
structure. As indicated before, traditional SDL Cs lack at least one of the aforementioned properties
making them not all suitable for the development of DBMSs, particularly, when the demands on the
DBMS are unpredictable. One of main characteristics of this new proposed model is that it makes
emphasis on activities that go back and forth between phases allowing either the incorporation of
new requirements, if needed, or the correction of incomplete or misunderstood requirements. The
idea is to allow for a system that is more flexible of the realities of developing a DBMS.
EX.NO:2 DATABASE DESIGN USING CONCEPTUAL MODELING (ER-EER)
TOP-DOWN APPROACH MAPPING CONCEPTUAL TO RELATIONAL
DATABASE AND VALIDATE USING NORMALIZATION
AIM

To create a database design using conceptual modeling (ER-EER) top-down approach

mapping conceptual to relational database and validate using normalization.

ALOGRITHM

Step 1: Identify entity types (entity type vs.attribute)

Step 2: Identify relationship types
Step 3: Identify and associate attributes with entity and relationship types
Step 4: Determine attribute domains
Step 5: Determine primary key attributes for entity types
Step 6: Associate (refined) cardinality ratio(s) with relationship types
Step 7: Design generalization/specialization hierarchies including constraints (includes
natural language statements as well)

Consider following databases and draw ER diagram and convert entities and
relationship to relation table for a given scenario.
1. COLLEGE DATABASE:
STUDENT (USN, SName, Address, Phone, Gender)
SEMSEC (SSID, Sem, Sec)
CLASS (USN, SSID)
SUBJECT (Subcode, Title, Sem, Credits)
IAMARKS (USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)
2. COMPANY DATABASE:
EMPLOYEE (SSN, Name, Address, Sex, Salary, SuperSSN,
DNo) DEPARTMENT (DNo, DName, MgrSSN, MgrStartDate)
DLOCATION (DNo,DLoc)
PROJECT (PNo, PName, PLocation, DNo)
WORKS_ON (SSN, PNo, Hours)
 Mapping entities and relationships to relation table (Schema Diagram)

RESULT

Thus the Database design using Conceptual modeling and validate using Normalization
was executed successfully
EX.NO:3 IMPLEMENT THE DATABASE USING SQL DATA DEFINITION WITH
CONSTRAINTS, VIEWS

AIM

To creating various constraints for an employee in a table.

ALGORITHM

STEP 1: Start querying after establishing connection in Run SQL Command Line
console window.
STEP 2: After creating table Employee with different fields
STEP 3: Execute various level of constraints in the employee table
STEP 4: If the exceptions is thrown, check the syntax and logics.
STEP 5: If the query is running successfully, stop querying.
QUERIES

TABLE SCHEMA

NOT NULL CONSTRAINT

CREATE TABLE COMPANY1(ID INT PRIMARY KEY NOT

NULL, NAME TEXT NOT NULL, AGE INT NOT NULL,
ADDRESS CHAR(50), SALARY REAL);

Output
UNIQUE CONSTRAINT

CREATE TABLE COMPANY3(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT
NULL,AGE INT NOT NULL UNIQUE, ADDRESS CHAR(50),SALARY REAL DEFAULT
50000.00);

Output

PRIMARY KEY

CREATE TABLE COMPANY4(ID INT PRIMARY KEY NOT NULL,

NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS
CHAR(50),SALARY REAL);

Output
 FOREIGN KEY

CREATE TABLE COMPANY6(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT
NULL, AGE INT NOT NULL, ADDRESS CHAR(50),SALARY REAL);

CREATE TABLE DEPARTMENT1(ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT
NULL,EMP_ID INT references COMPANY6(ID));

Output

CHECK CONSTRAINT

CREATE TABLE COMPANY5(ID INT PRIMARY KEY NOT NULL, NAME TEXTNOT NULL,
AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL CHECK(SALARY > 0));

Output

RESULT
Thus the Creation of a database and writing POSTGRESQL queries for various
constraints was created and executed successfully.
 INTRODUCTION TO VIEWS

A view is an object that gives the user a logical view of data from an underlying
table or tables. We can restrict what users can view by allowing them to see only a few
attributes/columns from a table.
Views may be created for the following:

 Simplifies the queries.

 It can be queried as a base table

 It provides data security

CREATION OF VIEW

CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE FROM COMPANY5;

INSERTING OF VIEW

INSERT INTO COMPANY_VIEW VALUES(2,’BBB’,19);

UPDATING VIEW

UPDATE COMPANY_VIEW SET AGE=10 WHERE NAME='AAA';

DELETING VIEW

DELETE FROM COMPANY_VIEW WHERE id=2;

DROP VIEW

DROP VIEW COMPANY_VIEW;

Object Name:
The name of the object for which you are creating the synonym. It can be one of
the following:
 Table
 View
 Sequence
 Stored Producer
 Function
 User Defined Object
 Synonym

RESULT
Thus the Creation of a database and writing POSTGRESQL queries for views was created and
executed successfully
EX.NO:4
Query the database using SQL Manipulation

Aim:

To create the Data Base manipulate the SQL Statement

DISPLAY THE CONTENT OF THE TABLE

\d CSE;

Output

CREATE A NEW TABLE FROM AN EXISTING TABLE WITH ALL FIELDS

Create table AI_DS as select * from CSE;

Output
 DISPLAY THE NEW TABLE FROM EXISTING TABLE

\d AI_DS;
Output

CREATE A NEW TABLE FROM AN EXISTING TABLE WITH SELECTED FIELDS

Create table EEE as select Reg_No, Name from CSE;

Output

DISPLAY A NEW TABLE FROM AN EXISTING TABLE WITH SELECTED FIELDS

\d EEE;

Output

RESULT

Thus the creation of database and writing a SQL queries to retrieve the information
from the table which is stored in the database was executed successfully.
 INTRODUCTION TO DATA DEFINITION LANGUAGE (DDL) AND DATA
MANIPULATION LANGUAGE (DML)

OVERVIEW OF SQL

The SQL language can be categorized as Data Definition Language (DDL), Data
Manipulation Language (DML), Data Control Language (DCL) and Transactional Control
Language (TCL).

SQL is not a Case Sensitive language.

SQL

DDL DML DCL TCL

DATA DEFINITION LANGUAGE (DDL):

DDL commands are used to build and modify the structure of the tables
and other objects in the database. When executing a DDL command, it takes
effect immediately.

The DDL commands are:

 CREATE : Used to create a new object in the database

 ALTER : It helps to change the object or field from existing
one
 RENAME : Rename the object
 TRUNCATE : Remove all the records from the table
 DROP : It is used to drop or remove the object or field

Syntax – CREATE

Create Table Table_Name (Column Name1 Data_type (Size), Column

Name2 Data_type (Size),.................................................);
 Syntax –ALTER

Adding New ALTER TABLE <Table_Name> ADD

Columns COLUMN(<New column_name> <Data_Type>
(Size),… N);

Dropping a Column ALTER TABLE <Table_Name> DROP COLUMN

From The Table (Column_ Name);

Modifying Existing ALTER TABLE <Table_ Name> RENAME (<Column

Table _Name> TO <New datatype>;

Syntax- RENAME

ALTER TABLE <old table_name> RENAME TO <new

table_name>;

Syntax – TRUNCATE

TRUNCATE TABLE <table name>;

DATA MANIPULATION LANGUAGE (DML):

It is used to retrieve, store, modify, delete, insert and update data in database. DML is
responsible for all forms of data modification in a database. These commands are used
for managing data within schema objects.

The DML commands are:

 SELECT : Retrieve data from a database

 INSERT : Insert data into a table
 UPDATE : Updates existing data within a table
 DELETE : Deletes all records from a table, the space for the
records which is remains.

Syntax- SELECT

Selecting the entire SELECT * FROM Table_name;

table

Selecting the particular SELECT Column_name1, Column_name2 FROM

field Table_name;
Syntax-INSERT

Inserting values without INSERT INTO Table_name VALUES (Value1,

specifying column Value2,……Value_N);

Inserting values with INSERT INTO Table_name

specifying column name (Column1,Column2,…Column_N) VALUES
(Value1, Value2,……Value_N);

Syntax- UPDATE

UPDATE Table_name SET Column1 = Value, Column2 = Value,…WHERE some_Column =

Syntax- DELETE

DELETE FROM Table_name WHERE some_Column = some_Value;

 TRANSACTION CONTROL LANGUAGE

(TCL) OVERVIEW OF TCL

Transaction Control Language (TCL) manages changes made by DML statements. A transaction
is a set of SQL statements, i.e. all the statement should execute successfully or none of the statement
should execute.

To control transaction Oracle does not made any changes unless we commit. If we
don’t commit the transaction and power goes off or system crashes then the transaction is roll backed.

TCL statements are follows:

 Commit : Make changes done in transaction permanently

 Rollback : Rollbacks the state of database to the last commit

point

 Save point : Use to specify a point in transaction to which can

be roll backed later.

COMMIT

Commit [work] [comment ‘your comment’];

Where;

Work & comment are optional.

ROLLBACK

rollback to savepoint-name;

SAVEPOINT

Savepoint savepoint-name;
 Ex. No: 5 Quering/Managing the database using SQL Programming

AIM
To Implement of Simple Programs in PL/SQL

DESCRIPTION

Basic Syntax

PL/SQL is a block-structured language, meaning that PL/SQL programs are divided and written in
logical blocks of code.
Each block consists of three sub-parts:
Declarations
This section starts with the keyword DECLARE. It is an optional section and defines all
variables, cursors, subprograms, and other elements to be used in the program.
Executable Commands
This section is enclosed between the keywords BEGIN and END and it is a mandatory section. It
consists of the executable PL/SQL statements of the program. It should have at least one executable line of
code, which may be just a NULL command to indicate that nothing should be executed.
Exception Handling
This section starts with the keyword EXCEPTION. This section is again optional and contains
exception(s) that handle errors in the program.

STORED PROCEDURE/FUNCTION

DEFINITION
A procedure or function is a logically grouped set of SQL and PL/SQL statements that perform
a specific task. They are essentially sub-programs. Procedures and functions are made up of,
• Declarative part
• Executable part
• Optional exception handling part

These procedures and functions do not show the errors.

 KEYWORDS AND THEIR PURPOSES
REPLACE: It recreates the procedure if it already exists. PROCEDURE: It is the name of the procedure
to be created.
ARGUMENT: It is the name of the argument to the procedure. Parenthesis can be omitted if no
arguments are present.
IN: Specifies that a value for the argument must be specified when calling the procedure i.e. used to pass
values to a sub-program. This is the default parameter.

OUT: Specifies that the procedure passes a value for this argument back to it’s calling environment after
execution ie. used to return values to a caller of the sub-program.
INOUT: Specifies that a value for the argument must be specified when calling the procedure and that
procedure passes a value for this argument back to it’s calling environment after execution.
RETURN: It is the datatype of the function’s return value because every function must return a value,
this clause is required.
PROCEDURES

A procedure is a named POSTGRESOL/SQL block which performs one or more specific task. This is
similar to a procedure in other programming languages. A procedure has a header and a body. The header
consists of the name of the procedure and the parameters or variables passed to the procedure. The body
consists or declaration section, execution section and exception section similar to a general
POSTGRSQL/SQL Block.

DROP TABLE if exists ACCOUNTS;

create table accounts (id int generated by default as identity, name varchar(100) not null,
balance dec(15, 2) not null, primary key(id) );

INSERT INTO ACCOUNTS(name, balance) VALUES ('Raju', 10000);

INSERT INTO ACCOUNTS(name, balance) VALUES ('Nikhil', 10000);

SELECT * FROM ACCOUNTS;

 create or replace procedure transfer(
sender int,
receiver int,
amount dec
)
language plpgsql
as $$
begin
-- subtracting the amount from the sender's account
update accounts
set balance = balance - amount
where id = sender;

-- adding the amount to the receiver's account

update accounts
set balance = balance + amount
where id = receiver;

commit;
end;$$;

call stored_procedure_name(argument_list); call transfer(1, 2, 1000);

 SELECT * FROM accounts;

CONSTRAINTS AND SECURITY USING TRIGGERS

DEFINITION

• A trigger is a statement that is executed automatically by the system as a side

effect of a modification to the database. The parts of a trigger are,
• TRIGGER STATEMENT: Specifies the DML statements and fires the trigger
body. It also specifies the table to which the trigger is associated.
• TRIGGER BODY OR TRIGGER ACTION: It is a PL/SQL block that is
executed when the triggering statement is used.
• TRIGGER RESTRICTION: Restrictions on the trigger can be achieved. The
different uses of triggers are as follows,
• To generate data automatically
• To enforce complex integrity constraints
• To customize complex securing authorizations
• To maintain the replicate table
• To audit data modifications
TYPES OF TRIGGERS

The various types of triggers are as follows,

• Before: It fires the trigger before executing the trigger statement.
• After: It fires the trigger after executing the trigger statement.
• For each row: It specifies that the trigger fires once per row.
• For each statement: This is the default trigger that is invoked. It specifies
that the trigger fires once per statement.
 TRIGGER FOR DISPLAYING GRADE OF THE STUDENT

CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE
INT NOT NULL, ADDRESS CHAR(50),SALARY REAL);

CREATE TABLE AUDIT( EMP_ID INT NOT NULL, ENTRY_DATE TEXT NOT NULL);

CREATE OR REPLACE FUNCTION auditlogfunc()

RETURNS TRIGGER AS $example_table$
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID,
current_timestamp); RETURN NEW;
END;
$example_table$ LANGUAGE plpgsql;
CREATE TRIGGER example_trigger AFTER INSERT ON
COMPANY FOR EACH ROW EXECUTE PROCEDURE
auditlogfunc();

INSERT INTO COMPANY

(ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32,
'California', 20000.00 );

SELECT * FROM pg_trigger;

SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND

relname='company';
DROP TRIGGER trigger_name;

RESULT

Thus Stored Procedures/Functions and Constraints and security using Triggers

were implemented successfully.
 Ex.No:06
Database design using Normalization – bottom-up approach

ER diagram:

Chen Notation

ORDER (OrderNum (key), OrderDate, SalesPerson)

ORDERITEMS (OrderNum (key)(fk) , ItemNum (key), PartNum, Quantity, Cost)

In the above example, in the ORDERITEMS Relation: OrderNum is the

Foreign Key and OrderNum plus ItemNum is the Composite
Key.

Chen Notation

In the ORDER Relation: OrderNum is the Key.

Representing Relationships

• 1:1 Relationships. The key of one relation is stored in the second relation. Look at
example queries to determine which key is queried most often.
 • 1:N Relationships.
Parent - Relation on the "1" side.
Child - Relation on the "Many" side.
• Represent each Entity as a relation.
Copy the key of the parent into the child relation.
• CUSTOMER (CustomerID (key), Name, Address, ...)
ORDER (OrderNum (key), OrderDate, SalesPerson, CustomerID (fk))
 M:N Relationships. Many to Many relationships can not be directly implemented in relations.
 Solution: Introduce a third Intersection relation and copy keys from
original two relations.

Chen Notation

SUPPLIER (SupplierID (key), FirmName, Address, ...) COMPONENT (CompID (key),

Description, ...) SUPPLIER_COMPONENT (SupplierID (key), CompID (key))
Note that this can also be shown in the ER diagram. Also, look for potential added
attributes in the intersection relation.

PROGRAM
CREATE TABLE the_table
( id SERIAL NOT NULL PRIMARY KEY, name varchar, a INTEGER,
b varchar, c varchar);
INSERT INTO the_table(name, a,b,c) VALUES ( 'Chimpanzee' , 1, 'mammals', 'apes' )
,( 'Urang Utang' , 1, 'mammals', 'apes' ),( 'Homo Sapiens' , 1, 'mammals', 'apes' )
,( 'Mouse' , 2, 'mammals', 'rodents' ),( 'Rat' , 2, 'mammals', 'rodents' )
,( 'Cat' , 3, 'mammals', 'felix' ),( 'Dog' , 3, 'mammals', 'canae' );
 -- [empty] table to contain the "squeezed out" domain {a,b,c}
CREATE TABLE abc_table( id SERIAL NOT NULL PRIMARY KEY,
a INTEGER, b varchar, c varchar, UNIQUE (a,b,c));

ALTER TABLE the_table ADD column abc_id INTEGER -- NOT NULL REFERENCES abc_table(id);

-- FK constraints are helped a lot by a supportive index.

CREATE INDEX abc_table_fk ON the_table (abc_id); WITH ins AS

(INSERT INTO abc_table(a,b,c)

SELECT DISTINCT a,b,c FROM the_table a RETURNING *)

UPDATE the_table ani SET abc_id = ins.id FROM ins WHERE ins.a = ani.a AND ins.b
= ani.b AND ins.c = ani.c

-- Now that we have the FK pointing to the new table,

-- we can drop the redundant columns.

ALTER TABLE the_table DROP COLUMN a, DROP COLUMN b, DROP COLUMN c;

SELECT * FROM the_table; SELECT * FROM abc_table;

-- show it to the world

SELECT a.*, c.a, c.b, c.c FROM the_table a JOIN abc_table c ON c.id = a.abc_id;

RESULT

Thus the ER Database design using E-R model and Normalization was
implemented successfully.
 Ex. No.07
Database Design using IDE /RAD tools(Eg., NetBeans, Visual
Studio)

AIM
To Implement Database Connectivity using ADO in VB
DESCRIPTION:
Procedure for Creating Table
CREATING TABLE-Form2
SELECT * FROM custable1;
CID CNAME CADDR CPHNO CEMAIL

101 xxx 9834123478 aaawwwcom

- 12kknagar
102 vvv 12ppnagar 945238912 www@com
103 fff 23yynagar 98431290 aa@com
CREATING TABLE-Form3

CREATE TABLE stockin (itemno number (4), itemname varchar2 (10), quantity number (5), price
number (5));
Table created.
SELECT * FROM stockin;;
ITEMNO ITEMNAME QUANTITY PRICE

1 SOAP 500 56
1 SOAP 500 56
2 OIL 200 100
3 HAMMAM 100 45
4 SNACKS 50 20
COMMIT;
Commit complete.
ALGORITHM FOR ADO CONNECTION:
After creating the table in ORACLE, go to start menu
Start --> Settings --> Control Panel --> Administrative tools --> Data Sources(ODBC) --> User DSN -->
Add --> Select ORACLE database driver --> OK.
One new window will appear. In that window, type data source name as table name created in ORACLE.
Type user name as secondcsea.
ALGORITHM FOR ADODC IN VISUAL BASIC:
In visual basic create tables, command buttons and then text boxes. In visual basic, go to start menu.
 Projects --> Components --> Microsoft ADO Data Control 6.0 for OLEDB --> OK.
 Now ADODC Data Control available in tool box.
 Drag and drop the ADODC Data Control in the form.
 Right click in ADODC Data Control, then click ADODC properties.
 One new window will appear.
 Choose general tab, select ODBC Data Sources name as the table name created in
ORACLE
 Choose authentication tab and select username password as secondcsea and secondcsea
 Choose record name-->select command type as adcmdTable.
 Select table or store procedure name as table created in ORACLE.
 Click Apply-->OK
 Set properties of each text box.
 Select the data source as ADODC1.
 Select the Data field and set the required field name created in table .

VB SCRIPT:

LOGIN FORM:

Private Sub Command1_Click ()

If Text1.Text = "admin" And Text2.Text = "admin123" Then MsgBox
("login successfully") Form2.Show
Else
MsgBox ("login failed") End If End Sub
ADD:
Private Sub Add_Click() Adodc1.Recordset.AddNew Textl.SetFocus End Sub
DELETE:
Private Sub Delete_Click()
If MsgBox ("DELETE IT?",vb OKCancel)= vbOK Then dodc1.Recordset.Delete End If
MsgBox "ONE ROW DELETED" Textl.Text - " "
Text2.Text - " "
Text3.Text - " " Text4.Text - " " Text5.Text - " " End Sub
SAVE:
Private Sub Save_Click()
If MsgBox ("SAVE IT?",vbOKCancel ) = vbOK Then Adodc1.Recordset.Update Else
Adodc1.Recordset.CancelUpdate
End If End Sub
FIND:
Private Sub Find_Click() Dim N as string

N = InputBox ("Enter the cid") Adodc1.Recordset.Find "cid=" & N If

Adodcl.Recordset.BOF or Adodc1.Recordset.EOF Then MsgBox "Record not
found" End If
End Sub
UPDATE:
Private Sub Update_Click()

Adodc1.Recordset.EditMode Adodc1.Recordset.Update End Sub

FIRST:
Private Sub First_Click() Adodc1.Recordset.MoveFirst End Sub
LAST:
Private Sub Last_Click() Adodc1.Recordset.MoveLast End Sub
NEXT:
Private Sub Next_Click() Adodc1.Recordset.MoveNext End Sub
PREVIOUS:
Private Sub Previous_Click() Adodc1.Recordset.MovePrevious End Sub
EXIT:
Private Sub Add_Click() Unload Me End
Sub
FUNCTION:
Function Calculate()
Text 5.Text=val(Text4.Text) + val (Text3.Text) End Function
OUTPUT

FORM1:

FORM2:
FORM3:

RESULT
Thus Database Connectivity using ADO in VB is implemented successfully.
 EX.NO:08
Database design using EER-to-ODB mapping

AIM
To implement the database design using EER-to-ODB mapping.
Algorithm
Step 1: For each regular entity type E
• Create a relation R that includes all the simple attributes of E.
• Include all the simple component attributes of composite attributes.
• Choose one of the key attributes of E as primary key for R.
• If the chosen key of E is composite, the set of simple attributes that form it will together
form the primary key of R.
Step 2: For each weak entity type W with owner entity type E
• Create a relation R, and include all simple attributes and simple components of composite
attributes of W as attributes of R.
• In addition, include as foreign key attributes of R the primary key attribute(s) of the
relation(s) that correspond to the owner entity type(s).
Step 3: For each binary 1:1 relationship type R
• Identify the relations S and T that correspond to the entity types participating in R.
Choose one of the relations, say S, and include as foreign key in S the primary key of T.
• It is better to choose an entity type with total participation in R in the role of S.
• Include the simple attributes of the 1:1 relationship type R as attributes of S.
• If both participations are total, we may merge the two entity types and the
relationship into a single relation.
Step 4: For each regular binary 1:Nrelationship type R
• Identify the relation S that represents the participating entity type at the N-
side of the relationship type.
• Include as foreign key in S the primary key of the relations T that represents the
other entity type participating in R.
• Include any simple attributes of the 1:N relationship type as attributes of S.
Step 5:For each binary M:N relationship type R
• Create a new relation S to represent R.
• Include as foreign key attributes in S the primary keys of the relations that represent the
participating entity types; their combination will form the primary key of S
• Also, include any simple attributes of the M:N relationship type as attributes of S.
Step 6: For each multi-valued attribute A
• Create a new relation R that includes an attribute corresponding to A plus the primary key
attribute K (as a foreign key in R) of the relation that represents the entity type or relationship type that
has A as an attribute.
• The primary key of R is the combination of A and K. If a multi-valued attribute is
composite, we include its components.
• However, if the participation constraint (min,max) of one of the entity types E
participating in the R has max =1, then the primary key of S can be the single foreign key attribute
that references the relation E’ corresponding to E
• This is because , in this case, each entity e in E will participate in at most one relationship
instance of R and hence can uniquely identify that relationship instance.
Step 7: To convert each super-class/sub- class relationship into a relational schema you must use
one of the four options available.
Let C be the super-class, K its primary key and A1, A2, …, An its remaining attributes and let S1,
S2, …, Sm be the sub-classes.
Option 7A (multiple relation option):
• Create a relation L for C with attributes Attrs(L) = {K, A1, A2, …, An} and PK(L) = K.
• Create a relation Li for each subclass Si, 1 < i < m, with the attributes
ATTRS(Li) = {K} U {attributes of Si} and PK(Li) = K.
• This option works for any constraints: disjoint or overlapping; total or partial.
Option 7B (multiple relation option):
• Create a relation Li for each subclass Si, 1 < i < m, with ATTRS(Li) = {attributes of Si}
U {K, A1, A2, …, An} PK(Li) = K
• This option works well only for disjoint and total constraints.
• If not disjoint, redundant values for inherited attributes.
• If not , entity not total belonging to any sub-class is lost.
EMPLOYEE

FNAM MINI LNAM SSN BDAT ADDRES SEX SALAR SUPERSS DN

E T E E S Y N O

DEPARTMENT

DNAME DNUMBER MGRSSN MGRSTARTDATE

 DEPT_LOCATIONS

DNUMBER DLOCATION

PROJECT

PNAME PNUMBER PLOCATION DNUM

WORKS_ON

ESSN PNO HOURS

DEPENDENT

ESSN DEPENDENT_NAME SEX BDATE RELATIONSHIP

Option 7C (Single Relation Option)

• Create a single relation L with attributes Attrs(L) = {K, A1, …, An} U
{attributes of S1} U… U
{attributes of Sm} U {T} and PK(L)=K

• This option is for specialization whose subclasses are DISJOINT, and T is a type attribute that
indicates the subclass to which each tuple belongs, if any. This option may generate a large number of null
values.
• Not recommended if many specific attributes are defined in subclasses (will result in many
null values!)
Option 7D (Single Relation Option)
• Create a single relation schema L with attributes Attrs(L) = {K, A1, …, An} U
{attributes of S1} U… U{attributes of Sm} U {T1, …, Tn} and PK(L)=K
• This option is for specialization whose subclasses are overlapping, and each Ti, 1 < i < m,
is a Boolean attribute indicating whether a tuple belongs to subclass Si.
• This option could be used for disjoint subclasses too.

RESULT

Thus, the database design using EER-to-ODB mapping was implemented

successfully
 EX.No:09
Object features of SQL-UDTs and sub-types, Tables using
UDTs, Inheritance, Method definition

AIM
To study about Object features of SQL-UDTs and sub-types, Tables using UDTs, Inheritance,
Method definition
ALGORITHM
The relational model with object database enhancements is sometimes referred to as the object-
relational model. Additional revisions were made to SQL in 2003 and 2006 to add features related to
XML.
The following are some of the object database features that have been included in SQL: Some type
constructors have been added to specify complex objects. These include the row type, which corresponds to
the tuple (or struct) constructor. An array type for specifying collections is also provided. Other collection
type constructors, such as set, list, and bag constructors, were not part of the original SQL/Object
specifications but were later included in the standard.
A mechanism for specifying object identity through the use of reference type is included.
Encapsulation of operations is provided through the mechanism of user-defined types (UDTs) that may
include operations as part of their declaration. These are somewhat similar to the concept of abstract data
types that were developed in programming languages. In addition, the concept of user-defined routines
(UDRs) allows the definition of general methods (operations).
Inheritance mechanisms are provided using the keyword UNDER.
1. User-Defined Types and Complex Structures for Objects
To allow the creation of complex-structured objects, and to separate the declaration of a type from
the creation of a table, SQL now provides user-defined types (UDTs). In addition, four collection types
have been included to allow for multivalued types and attributes in order to specify complex-structured
objects rather than just simple (flat) records. The user will create the UDTs for a particular application as
part of the database schema.
A UDT may be specified in its simplest form using the following syntax:
CREATE TYPE TYPE_NAME AS (<component declarations>);
First, a UDT can be used as either the type for an attribute or as the type for a table. By using a
UDT as the type for an attribute within another UDT, a complex structure for objects (tuples) in
a table can be created, much like that achieved by nesting type construction.
 2. Object Identifiers Using Reference Types
Unique system-generated object identifiers can be created via the reference type in the latest
version of SQL. For example, in Figure 11.4(b), the phrase:
REF IS SYSTEM GENERATED
indicates that whenever a new PERSON_TYPE object is created, the system will assign it a unique
system-generated identifier. It is also possible not to have a system-generated object identifier and use the
traditional keys of the basic relational model if desired.
In general, the user can specify that system-generated object identifiers for the individual rows in a
table should be created. By using the syntax:
REF IS <OID_ATTRIBUTE> <VALUE_GENERATION_METHOD> ;
the user declares that the attribute named <OID_ATTRIBUTE> will be used to identify individual
tuples in the table. The options for <VALUE_GENERATION _METHOD> are SYSTEM GENERATED or
DERIVED. In the former case, the system will automatically generate a unique identifier for each tuple. In the
latter case, the traditional method of using the user-provided primary key value to identify tuples is applied.
3. Creating Tables Based on the UDTs
For each UDT that is specified to be instantiable via the phrase INSTANTIABLE (see Figure
11.4(b)), one or more tables may be created. This is illustrated in Figure 11.4(d), where we create a table
PERSON based on the PERSON_TYPE UDT. Notice that the UDTs in Figure 11.4(a) are noninstantiable,
and hence can only be used as types for attributes, but not as a basis for table creation. In Figure 11.4(b), the
attrib-ute PERSON_ID will hold the system-generated object identifier whenever a new PERSON record
(object) is created and inserted in the table.
4. Encapsulation of Operations
In SQL, a user-defined type can have its own behavioral specification by specifying methods (or
operations) in addition to the attributes. The general form of a UDT specification with methods is as follows:
CREATE TYPE <TYPE-NAME> (<LIST OF COMPONENT ATTRIBUTES AND THEIR
TYPES> <DECLARATION OF FUNCTIONS (METHODS)> );
For example, in Figure 11.4(b), we declared a method Age() that calculates the age of an individual
object of type PERSON_TYPE.
The code for implementing the method still has to be written. We can refer to the method
implementation by specifying the file that contains the code for the method, or we can write the actual code
within the type declaration itself (see Figure 11.4(b)).
SQL provides certain built-in functions for user-defined types. For a UDT called TYPE_T, the
constructor function TYPE_T( ) returns a new object of that type. In the new UDT object, every attribute is
initialized to its default value. An observer function A is implicitly created for each attribute A to read
its value. Hence, A(X) or X.A returns the value of attribute A of TYPE_T if X is of type TYPE_T.
 A mutator function for updating an attribute sets the value of the attribute to a new value. SQL
allows these functions to be blocked from public use; an EXECUTE privilege is needed to have access to
these functions.
In general, a UDT can have a number of user-defined functions associated with it. The syntax
is
INSTANCE METHOD <NAME> (<ARGUMENT_LIST>) RETURNS
<RETURN_TYPE>;
Two types of functions can be defined: internal SQL and external. Internal functions are
written in the extended PSM language of SQL (see Chapter 13). External functions are written in
a host language, with only their signature (interface) appearing in the UDT definition. An external function
definition can be declared as follows:
DECLARE EXTERNAL <FUNCTION_NAME> <SIGNATURE> LANGUAGE
<LANGUAGE_NAME>;
Attributes and functions in UDTs are divided into three categories: PUBLIC (visible at the UDT
interface)
PRIVATE (not visible at the UDT interface) PROTECTED (visible only to subtypes)
It is also possible to define virtual attributes as part of UDTs, which are computed and updated
using functions.
5. Specifying Inheritance and Overloading of Functions
Recall that we already discussed many of the principles of inheritance in Section 11.1.5. SQL has
rules for dealing with type inheritance (specified via the UNDER keyword). In general, both attributes and
instance methods (operations) are inherited. The phrase NOT FINAL must be included in a UDT if subtypes
are allowed to be created under that UDT (see Figure 11.4(a) and (b), where
PERSON_TYPE,STUDENT_TYPE, and EMPLOYEE_TYPE are declared to be NOT FINAL). Associated
with type inheritance are the rules for overloading of function implementations and for resolution of function
names.
These Inheritance rules can be summarized as follows: All attributes are inherited.
The order of supertypes in the UNDER clause determines the inheritance hierarchy.
An instance of a subtype can be used in every context in which a supertype instance is used.
A subtype can redefine any function that is defined in its supertype, with the restriction that the
signature be the same.
When a function is called, the best match is selected based on the types of all arguments.
 For dynamic linking, the runtime types of parameters is considered.
Consider the following examples to illustrate type inheritance, Suppose that we want to create
two subtypes of PERSON_TYPE: EMPLOYEE_TYPE and STUDENT_TYPE. In addition, we also create
subtype MANAGER_TYPE that inherits all the attributes (and methods) of
EMPLOYEE_TYPE but has an additional attribute DEPT_MANAGED. These subtypes are shown in Figure
11.4(c).
In general, we specify the local attributes and any additional specific methods for the subtype,
which inherits the attributes and operations of its supertype.
Another facility in SQL is table inheritance via the supertable/subtable facility. This is also
specified using the keyword UNDER (see Figure 11.4(d)). Here, a new record that is inserted into a subtable,
say the MANAGER table, is also inserted into its supertables EMPLOYEE and PERSON. Notice that when a
record is inserted in MANAGER, we must provide values for all its inherited attributes. INSERT, DELETE,
and UPDATE operations are appropriately propagated.
6. Specifying Relationships via Reference
A component attribute of one tuple may be a reference (specified using the key-word REF) to a
tuple of another (or possibly the same) table. An example is shown in Figure 11.4(e).
The keyword SCOPE specifies the name of the table whose tuples can be referenced by the
reference attribute. Notice that this is similar to a foreign key, except that the system-generated value is used
rather than the primary key value.
SQL uses a dot notation to build path expressions that refer to the component attributes of tuples
and row types. However, for an attribute whose type is REF, the dereferencing symbol –> is used. For
example, the query below retrieves employees working in the company named ‘ABCXYZ’ by querying the
EMPLOYMENT table:
SELECT E.Employee–>NAME
FROM EMPLOYMENT AS E
WHERE E.Company–>COMP_NAME = ‘ABCXYZ’;
In SQL, –> is used for dereferencing and has the same meaning assigned to it in the C
programming language. Thus, if r is a reference to a tuple and a is a component attribute in that tuple, then r –
> a is the value of attribute a in that tuple.

RESULT
Thus Object features of SQL-UDTs and sub-types, Tables using UDTs, Inheritance, Method
definition was successfully executed.
 EX.NO:10 Querying the Object-relational database using Object
Query language

AIM
To query the Object-relational database using Objet Query language.

OQL Commands
The tables used for the above program is,

Staffmanagers Table

Staffemployee Table

Staffcontractors Table
i) Creating the tables

CREATE TABLE staffmanagers(EmployeeID int, EmployeeName varchar(20), Department

varchar(15),Gender varchar(7),Age Int);
\d staffmanagers;
Insert into staffmanagers(EmployeeID , EmployeeName , Department,Gender,Age)values
(1,'Matt','Development','Male',28);

Creating table staffemployee

CREATE TABLE staffemployee(EmloyeeID int,Name varchar(15),Skills varchar(30), Gender

varchar(8),Age int);
INSERT INTO staffemployee(EmployeeID,Name,Skills,Gender,Age) Values (6,’Paul’, ’HTML,C+
+,Java’,’Male’,26);

SELECT * FROM staffemployee;

Creating table staffcontractors

CREATE TABLE staffcontractors(EmployeeID int,Name varchar(20),Gender varchar(20), Age

int,Extrainfo varchar(50));
INSERT INTO staffcontractors(EmployeeID,Name,Gender,Age,Extrainfo) VALUES(1,
’Richard’,’Male’,23,’ContractLength=1 month;Department = Operations’);
SELECT * FROM staffcontractors;

ii) Counting number of rows in the table

SELECT count(*) FROM staffmanagers;

iii) Conditional test by using the like operation

SELECT Employeename,Department FROM staffmanagers WHERE Name LIKE ‘%V%’;

SELECT Name,Age FROM staffcontractors WHERE Gender <> 'Male' AND Age <> 20;

iv) Use of OR and AND operators

SELECT Employeename,Age FROM staffmanagers WHERE Employeename = 'Vengat' OR Employeename =

'Mega';

v) Using Distinct Operator

SELECT DISTINCT Department FROM staffmanagers;

vi) Selecting the common values from both tables

SELECT Employeename ,Age FROM staffmanagers WHERE Employeename in ((SELECT Name

FROM staffcontractors));

vii) Deleting the rows in the table

DELETE FROM staffmanagers WHERE Employeename = 'Vengat';

RESULT

The Object-relational databases query using Object Query Language is executed successfully.
 EX.NO:1
1 INTERNAL MARKS CALCULATION PORTAL USING PHP AND MYSQL

AIM:
To develop a project for internal marks calculation portal using front end tool with backend tool.
PROCEDURE:

Step 1: In index page the user has to select Department, Year, Subject from a dropdown list.
Step 2: From the selected criteria, the user has to enter the marks for the specific subject is displayed.
Step 3: Marks of the students are entered into the marks field and ‘submit’ button is clicked.
Step 4: When ‘submit’ is clicked, the marks are stored into the specific table created for that subject in a
database called int_marks.
Step 5: Steps 3 & 4 are repeated for all the other subjects.
Step 6: When all the marks for the subjects are entered, marks of individual subjects are displayed when
view button is clicked.
Step 7: Also, a button called filter is also created to view a filtered marklist like failures, average, toppers and
so on.

CODING:

Mark entry for Subject CA

<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/meyer-reset/2.0/reset.min.css">
<style >
a
{
color: yellow;
text-align: center;
font-size: 18px;
}
</style>

<link rel="stylesheet" href="css/style1.css">

<?php
 $conn = mysqli_connect('127.0.0.1','root','','int');
if(isset($_POST["submit"]))
{
$marks1 = $_POST["marks1"];
$marks2 = $_POST["marks2"];
$marks3 = $_POST["marks3"];
$marks4 = $_POST["marks4"];
$marks5 = $_POST["marks5"];
$marks6 = $_POST["marks6"];
$marks7 = $_POST["marks7"];
$marks8 = $_POST["marks8"];
$marks9 = $_POST["marks9"];
$marks10 = $_POST["marks10"];

$sql = "INSERT INTO subintca(regno,name,ca) VALUES (711217104001,'ABINAYA.R','$marks1'),

(711217104002,'ANANDHI.H','$marks2'),(711217104003,'ANISH FATHIMA.A','$marks3'),
(711217104004,'ANUSAYA.R','$marks4'),(711217104005,'AYURLIN MONISHA.K','$marks5'),
(711217104006,'CHANDHINI DEVI.S','$marks6'),(711217104007,'CHANDRU.P','$marks7'),
(711217104008,'DEEPAN KUMAR.D','$marks8'),(711217104009,'DEEPA TOPPU','$marks9'),
(711217104010,'DHARANI.R','$marks10')";
if (mysqli_query($conn,$sql))
{
echo "Submission done successfully";
}
}
mysqli_close($conn);
?>
<title> CA Mark Entry</title>
</head>
<style >
h2
{
text-align: center;
font-size: 40px;
}
h4
{
text-align: left;
font-size: 26px;
 }
h4
{
text-align: right;
font-size: 26px;
}
</style>
<body>
<div class="container">
<h2 >Marks Entry</h2>
<hr>
<form method="post">
<div class="form-group">
<h4 >Subject:Computer Architecture</h4>
<h4>Faculty:Mrs.Mallika</h5>
<form title="Marks" >
<table align="center" class="table table-hover table-responsive-sm">
<th>RegNo</th>
<th>Names</th>
<th>Marks</th>
<tr>
<td>711217104001</td>
<td>ABINAYA.R</td>
<td><input type="number" name="marks1" size=12 required max="100"></td>
</tr>
<tr>
<td>711217104002</td>
<td>ANANDHI.H</td>
<td><input type="number" name="marks2" size=12 required max="100"></td>
</tr>
<tr>
<td>711217104003</td>
<td>ANISH FATHIMA.A</td>
<td><input type="number" name="marks3" size=12 required max="100"></td>
</tr>
<tr>
<td>711217104004</td>
<td>ANUSAYA.R</td>
<td><input type="number" name="marks4" size=12 required max="100"></td>
 </tr>
<tr>
<td>711217104005</td>
<td>AYURLIN MONISHA.K</td>
<td><input type="number" name="marks5" size=12 required max="100"></td>
</tr>
<tr>
<td>711217104006</td>
<td>CHANDHINI DEVI.S</td>
<td><input type="number" name="marks6" size=12 required max="100"></td>
</tr>
<tr>
<td>711217104007</td>
<td>CHANDRU.P</td>
<td><input type="number" name="marks7" size=12 required max="100"></td>
</tr>
<tr>
<td>711217104008</td>
<td>DEEPAN KUMAR.D</td>
<td><input type="number" name="marks8" size=12 required max="100"></td>
</tr>
<tr>
<td>711217104009</td>
<td>DEEPA TOPPU.A</td>
<td><input type="number" name="marks9" size=12 required max="100"></td>
</tr>
<tr>
<td>711217104010</td>
<td>DHARANI.R</td>
<td><input type="number" name="marks10" size=12 required max="100"></td>
</tr>
</table>
</form>
</div>
</div>
<script src='http://cdnjs.cloudflare.com/ajax/libs/jquery/2.1.3/jquery.min.js'></script>

<script src="js/index.js"></script>
</body>
</html>
Database Connection

<?php

$conn = mysqli_connect('127.0.0.1','root','','int');
if (mysqli_query($conn,$sql))
{
echo "Submission done successfully";

}mysqli_close($conn);

?>
Screenshots:
RESULT:
Thus, Internal Marks Portal - project has been created successfully.