DDL Data Definition Language (DDL) statements are used to define the database struct ure or schema. Some examples: CREATE - to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database TRUNCATE - remove all records from a table, including all spaces allocated for t he records are removed,rollback inot possible,faster than DELETE. COMMENT - add comments to the data dictionary RENAME - rename an object DML Data Manipulation Language (DML) statements are used for managing data within sc hema objects. Some examples: SELECT - retrieve data from the 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 remain,ROLL BACK is possible,slower than TRUNCATE. MERGE - UPSERT operation (insert or update) CALL - call a PL/SQL or Java subprogram EXPLAIN PLAN - explain access path to data LOCK TABLE - control concurrency DCL Data Control Language (DCL) statements. Some examples: GRANT - gives user's access privileges to database REVOKE - withdraw access privileges given with the GRANT command TCL Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transaction s. COMMIT - save work done SAVEPOINT - identify a point in a transaction to which you can later roll back ROLLBACK - restore database to original since the last COMMIT SET TRANSACTION - Change transaction options like isolation level and what rollb ack segment to use? Attribute types: ? Simple and composite attributes. ? Single-valued and multi-va lued attributes ? E.g. multivalued attribute: phone-numbers ? Derived attributes ? Can be computed from other attributes Simple attribute attribute consists of a single atomic value. A simple attribute cannot b e subdivided. For example the attributes age, sex etc are simple attributes.? A composite attribute is an attribute that can be further subdivided. For examp le the attribute ADDRESS can be subdivided into street, city, state, and zip cod e
Simple Attribute: Attribute that consist of a single atomic value. Example: Salary, age etc Composite Attribute
: Attribute value not atomic.
Example :
Address : Name
House_no:City:State : First Name: Middle Name: Last Name ?
Single Valued and Multi Valued attribute A single valued attribute can have only a single value. For example a person can have only one 'date of birth', 'age' etc. That is a single valued attributes ca n have only single value. But it can be simple or composite attribute.That is 'd ate of birth' is a composite attribute , 'age' is a simple attribute. But both a re single valued attributes. Multivalued attributes can have multiple values. For instance a person may have multiple phone numbers,multiple degrees etc.Multivalued attributes are shown by a double line connecting to the entity in the ER diagram. Single Valued Attribute: Attribute that hold a single value Example1: Age Exampe2: City Example3:Customer id Multi Valued Attribute: Attribute that hold multiple values. Example1: A customer can have multiple phone numbers, email id's etc Example2: A person may have several college degrees? Stored and Derived Attributes The value for the derived attribute is derived from the stored attribute. For ex ample 'Date of birth' of a person is a stored attribute. The value for the attri bute 'AGE' can be derived by subtracting the 'Date of Birth'(DOB) from the curre nt date. Stored attribute supplies a value to the related attribute. Stored Attribute: An attribute that supplies a value to the related attribute. Example: Date of Birth Derived Attribute: An attribute that s value is derived from a stored attribute. Example : age, and it s value is derived from the stored attribute Date of Birth. Complex Attribute A complex attribute that is both composite and multi valued.
Mapping Cardinalities ? Express the number of entities to which another entity can be associated via a relationship set. ? Most useful in describing binary relationship sets. ? For a binary relationship set the mapping cardinality must be one of the following ty pes: One to one ? One to many ? Many to one ? Many to many? relationship? Rectangles represent entity sets. ? Diamonds represent relationship sets. ? Line s link attributes to entity sets and entity sets to relationship sets. ? Ellipse s represent attributes ? Double ellipses represent multivalued attributes. ? Das
