INTEGRITY CONSTRAINTS

Integrity constraints provide a way of ensuring that changes made to the database by authorised users do not result in a loss of data consistency. An integrity constraint can be any arbitrary predicate applied to the database. They may be costly to evaluate, so we will only consider integrity constraints that can be tested with minimal overhead.

DOMAIN CONSTRAINTS   

 

Domain constraints specify that the value of each attribute A must be an atomic value from the domain dom (A) for that attribute. Some data types associated with domains are given below;  

• Standard numeric data types for integers (such as short-integer, integer, long-integer). 

• Real numbers (float and double-precision float). 

•         Characters, fixed-length strings, and variable-length strings.

ENTITY INTEGRITY

Entity integrity is a property that ensures that no records are duplicated and that no attributes that make up the primary key are NULL. It is the one of the properties necessary to ensure the consistency of the database.

Entity Integrity ensures that there are no duplicate records within the table and that the field that identifies each record within the table is unique and never null.

The existence of the Primary Key is the core of the entity integrity. Entity integrity specifies that the Primary Keys on every instance of an entity must be kept, must bt unique and must have values other than NULL.

REFERENTIAL INTEGRITY

Referential integrity is usually enforced by the combination of a primary key or candidate key (alternate key) and a foregin key. A foregin value must match an existing value of the parent table.Foregin hey based on data values and are purely logical not physical.

DATA TYPES

The data types available with SQL are given below;

Internal Data type	Description
VARCHAR2 (size)	Variable length character string maximum length size bytes. Maximum size is 2000 and minimum is 1. You must specify size for a VARCHAR2.
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.
LONG	Character data of variable length up to 2 gigabytes, or 231-1 bytes.
DATE	Valid date range from January 1,4712 BC to December 31, 4712 AD.
RAW (size)	Raw binary data of length size bytes. Maximum size is 255 bytes. You must specify size of a RAW value.
LONG RAW	Raw binary data of variable length up to 2 gigabytes.
ROWID	Hexadecimal string representing the unique address of a row in its table. This data type is primarily for values returned by the ROWID pseudo column.
CHAR(size)	Fixed length character data of length size byte. Maximum size is 255. Default and minimum size is 1 byte.

E-R DIAGRAMS

The overall logical structure of a database can be expressed graphically by E-R diagram. The entity-relationship model can be conveniently represented in a pictorial form using drawing tools.

1. Rectangles: represent entity set.

2. Ellipses: represent attributes.

3. Diamonds: represent relationships among entity sets.

4. Lines: link attributes to entity sets and entity sets to relationships.

5. Key attributes: have their names underlined.

6. Derived attributes: are shown in dotted ovals.

7. Double rectangular: represent weak entity types and their identifying relationship are shown by double diamonds.

8. Dotted line: The partial key of the weak entity type.

ENTITY TYPE AND ENTITY SET

Entity Type : An entity type defines a collection of entities that have the same attribute. An entity is an instance of an entity type. All entities having the same set of properties are grouped into an entity type.

Entity sets : An entity set is an collection of similar entities, which share same properties or attributes. Each entity set has a key and each attribute has a domain. The individual entities that constitute a set are said to be extension of the entity set.

ENTITY AND ATTRIBUTES

Entity : An entity is a "thing" in real world, with an independent existence. It may have a physical existence in the real world or it may be an object with a conceptual existence.

Each entity has a set of particular properties that completely describe it, in the context of the mini-world model.

Attributes : The entity properties are called attributes. Attributes are properties used to describe an entity; for example an Employee entity may have a Name, Employee number, Address, Sex, Birth Date etc.

Several types of attributes occur in the ER model. They are;

1. Simple : Each entity has a single atomic value for the attribute.

2. Composite : An attribute may be composed of several components.

3. Multi-valued : An entity may assume multiple values.

OVERALL SYSTEM STRUCTURE

Database systems are partitioned into modules for different functions. Some functions may be provided by the operating system. The components include;

1. File manager : Its manages allocation of disk space and data structures used to represent information on disk.

2. Database manager : The interface between low-level data and application programs and queries.

3. Query processor : Its translates statement in a query language into low-level instruction the database manager understands.

4. DML Precompiler : Converts DML statements embedded in an application program to normal procedure calls in a host language. The precompiler interacts with the query processor.

5. DDL Compiler : Converts DDL statements to a set of tables containing metadata stored in a data dictionary.

6. Data Files : Store the database itself.

7. Data dictionary : Stores information about the structure of the database. It is used heavily. Great emphasis should be placed on developing a good design and efficient implementation of the dictionary.

8. Indices : provide fast access to data items holding particular values.

                                                                                           Database System Structure

DATABASE ADMINISTRATOR (DBA)

A database administrator (DBA) directs or performs all activities related to maintaining a successful database environment. Responsibilities include designing, implementing, and maintaining the database system; establishing policies and procedures pertaining to the management, security, maintenance, and use of the database management system; and training employees in database management and use. A DBA is expected to stay abreast of emerging technologies and new design approaches. Typically, a DBA has either a degree in Computer Science and some on-the-job training with a particular database product or more extensive experience with a range of database products. A DBA is usually expected to have experience with one or more of the major database management products, such as Structured Query Language, SAP, and Oracle-based database management software.

Role of the DBA

1. Database Tuning: Tweaking any of several parameters to optimize performance, such as server memory allocation, file fragmentation and disk usage.

2. Database Security: Ensuring that only authorized users have access to the database and fortifying it against any external, unauthorized access.

3. Backup and Recovery: It is a DBA's role to ensure that the database has adequate backup and recovery procedures in place to recover from any accidental or deliberate loss of data.

4. Producing Reports from Queries: DBAs are frequently called upon to generate reports by writing queries, which are then run against the database.