Entity Relationship Diagram

Entity Relationship Diagram

ER Diagram: logical database design

Degree of Relationship

One to one; (1:1) - one department - one HOD
One to many: (1:M) - One HOD - many Teacher
Many to one (M:1) - Many teachers - HOD
Many to Many (M:N) - Many Courses - Many students

Normalization: It’s a process of efficiently organizing data in a database. There are two goals of Normalization Process: eliminate redundant data (for example, storing the same data in more than one table) and ensure data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

Description of Normalization Forms:

First Normal Form (1NF) sets the very basic rules for an organized database:
- Eliminate duplicative columns from the same table.
- Create separate tables for each group of related data and identity each row with a unique column (Primary Key)

Second Normal Form (2NF) further addresses the concept of removing duplicative data:
- Remove subsets of data that apply to multiple rows of a table and place them in separate rows.
- Create relationships between these new tables and their predecessors through the use of foreign keys.

Third Normal Form (3NF) goes one large step further:
- Removes columns that are not dependant upon the primary key.

Finally Forth Normal Form (4NF), also known as Boyce- Codd normal form (BCNF) has one requirement:
- A relation is in BCNF if and only if determinant is a candidate key.

Relational Database Management System (RDBMS)

A Relational Database Management System (RDBMS) is an information system that presents information as rows contained in a collection of tables, each table possessing a set of one or more columns.

Object-Oriented Relational Database Management System (OORDBMS)

An Object-Oriented Relational Database Management System (OORDBMS) integrates a DBMS with the concepts of object-oriented programming. It tries to collate the persistence of a DBMS with the expressiveness of an object-oriented programming language. An Object- oriented databse thus stores persistent objects permanaetly on secondary storage. An OORDBMS permits these objects to be shared among different applications by provididng necessary DBMS functions such as indexing, concurrencey control and recovery. The main advantage of using object –orriented concepts to design is that databse is that such a databse is fully compatible with object – oriented applications and systems. Morever, an OORDBMS is oriented towards operations on single objects. RDBMSs are very inefficient in their performance with single objects.

Primary key:

In a well- designed relational database every table has some column or combination of columns whose values uniquely identify each row in the table. This column is called the primary key.

Foreign Key:

A column in one table whose value matches the primary key in some other table is called as a foreign key.

Oracle DATA Type

S.I	Data Type	Description
1	CHAR	Fixed-length character data of length size bytes. Maximum size is 2000 bytes or characters.
2	NCHAR	Fixed-length character data of length size characters. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.
3	VARCHAR2	Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters.
4	NVARCHAR2	Variable-length character string having maximum length size characters. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes.
5	NUMBER (p,s)	Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
6	INTEGER	Stores integer numbers. An Integer number does not contains a floating point.
7	DATE	Valid date range from January 1, 4712 BC to December 31, 9999 AD.
8	LONG	Character data of variable length up to 2 gigabytes, or 231 -1 bytes.
9	BLOB	A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).
10	CLOB/NCLOB	A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size). NCLOB: - Stores national character set data.
11	RAW (size)	Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
12	LONG RAW	Raw binary data of variable length up to 2 gigabytes. LONG RAW Can be used to stores graph, sound, documents or arrays of binary data.
13	BFILE	Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.
New Data Type in Oracle 9i
14	TIMESTAMP (fractional_seconds_precision)	Allow the time to be stored as a data with fraction of seconds. fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field.
15	TIMESTAMP (fractional_seconds_precision) WITH TIME ZONE	All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field.
16	TIMESTAMP (fractional_seconds_precision) WITH LOCAL TIME ZONE	All values of TIMESTAMP WITH TIME ZONE, with the following exceptions: · Data is normalized to the database time zone when it is stored in the database. · When the data is retrieved, users see the data in the session time zone.
17	INTERVAL YEAR (year_precision) TO MONTH	Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2.
18	INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision)	Allows time to be stored as an interval of days to hours, minutes and second. Useful in presenting the precise difference between two date time values
New Datatypes in 10g
19	BINARY_FLOAT	Stores a single precision 32-bit floating-point number.
20	BINARY_DOUBLE	Stores a single precision 64-bit floating-point number.

Example of The New data Type In Oracle 9i

TIMESTAMP	SQL> create table emp23 (empno number(2), start_date timestamp(7)) ; SQL> insert into emp23 values (23,'02-may-2005') ; SQL> select * from emp23 ; EMPNO START_DATE --------------------------------------------------------------------------- 23 02-MAY-20 05.00.00.0000000 AM
timestamp with time zone	SQL> create table emp23 (empno number(2), start_date timestamp with time zone) ; SQL> insert into emp23 values (23,'15-nov-04 09:34:34 AM') ; SQL> select * from emp23 ; EMPNO START_DATE --------------------------------------------------------------------------- 23 15-NOV-04 09.34.34.000000 AM +05:30
TIMESTAMP (fractional_seconds_precision) WITH LOCAL TIME ZONE	SQL> create table emp23 (empno number(2), order_date timestamp with local time zone) ; SQL> insert into emp23 values (23,'15-Nov-2004 09:34:24 AM') ; SQL> select * from emp23 ; EMPNO ORDER_DATE --------------------------------------------------------------------------- 23 15-NOV-04 09.34.24.000000 AM
INTERVAL YEAR TO MONTH	SQL> create table emp23 (empno number(2), loan_duration interval year (3) to Month) ; SQL> insert into emp23 values (23,interval '120' month(3)) ; SQL> select to_char(sysdate+loan_duration,'dd-mon-yyyy') from emp23 ; TO_CHAR(SYS ------------------ 12-may-2015
INTERVAL DAY TO SECOND	SQL> create table emp23 (empno number(2), day_duarion interval day (3) to second) ; SQL> insert into emp23 values (23,interval '180' day(3)) ; SQL> select sysdate+day_duarion from emp23 ; SYSDATE+D --------- 08-NOV-05