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 |
| Fixed-length character data of length |
2 |
| Fixed-length character data of length |
3 |
| Variable-length character string having maximum length |
4 |
| Variable-length character string having maximum length |
5 |
| Number having precision |
6 |
| Stores integer numbers. An Integer number does not contains a floating point. |
7 |
| Valid date range from January 1, 4712 BC to December 31, 9999 AD. |
8 |
| Character data of variable length up to 2 gigabytes, or 231 -1 bytes. |
9 |
| A binary large object. Maximum size is (4 gigabytes - 1) * (database block size). |
10 |
| 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).
|
11 |
| Raw binary data of length |
12 |
| 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 |
| 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. |
| ||
14 |
| Allow the time to be stored as a data with fraction of seconds. |
15 |
| All values of |
16 |
| All values of · 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 |
| Stores a period of time in years and months, where |
18 |
| 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 |
| Stores a single precision 32-bit floating-point number. |
20 |
| Stores a single precision 64-bit floating-point number. |
Example of The New data Type In Oracle 9i
| 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 |
| 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 |
| 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 |
| 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 |