Purpose of Database Systems
A database management system (DBMS) is system software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data.
Flat-File or card index Databases - contain one file. The records can only be accessed in a limited number of ways, and the number of fields in a record will be limited.
Problems with flat-file approach system
1. Data redundancy is found- the presence of duplicate data in multiple data files e.g.
Customer Name, NI number, Address is present in several different files on several
different systems. Errors are generated, time entering data is wasted, computer resources
are needlessly taken up and updates can be an enormous problem.
2. Data inconsistency will become a problem e.g. information is duplicated in each system
and may be updated in most systems but not necessarily in all – the savings account and
loan account may have different addresses for a particular customer.
3. Lack of data independence. (Data dependence refers to the close relationship between
data stored in files and the specific software programs needed to update and maintain
those files). In the bank imagine cases where any change in data format or structure
requires a change in all the programs that access the data. e.g. effort required to change
from 3-digit STD to 4-digit STD may be very difficult.
4. Data lacks integrity , i.e. this is the quality by which information from the sysstem can
be trusted. The problems already stated show that information can be out of date, can
have different values in different parts of the system, can be inaccurate etc.
5. Lack of flexibility i.e. creating reports, e.g. in this system a summary of account balances
in each separate account would require separate reports for each open account or cutting
and pasting into a wordprocessor file – not a satisfactory state of affairs.
6. Data not share Similarly if different departments want to access data this would be very awkward.
7. Lack of security i.e. there are no restrictions on who can see what.
A database management system (DBMS) is system software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data.
Flat-File or card index Databases - contain one file. The records can only be accessed in a limited number of ways, and the number of fields in a record will be limited.
Problems with flat-file approach system
1. Data redundancy is found- the presence of duplicate data in multiple data files e.g.
Customer Name, NI number, Address is present in several different files on several
different systems. Errors are generated, time entering data is wasted, computer resources
are needlessly taken up and updates can be an enormous problem.
2. Data inconsistency will become a problem e.g. information is duplicated in each system
and may be updated in most systems but not necessarily in all – the savings account and
loan account may have different addresses for a particular customer.
3. Lack of data independence. (Data dependence refers to the close relationship between
data stored in files and the specific software programs needed to update and maintain
those files). In the bank imagine cases where any change in data format or structure
requires a change in all the programs that access the data. e.g. effort required to change
from 3-digit STD to 4-digit STD may be very difficult.
4. Data lacks integrity , i.e. this is the quality by which information from the sysstem can
be trusted. The problems already stated show that information can be out of date, can
have different values in different parts of the system, can be inaccurate etc.
5. Lack of flexibility i.e. creating reports, e.g. in this system a summary of account balances
in each separate account would require separate reports for each open account or cutting
and pasting into a wordprocessor file – not a satisfactory state of affairs.
6. Data not share Similarly if different departments want to access data this would be very awkward.
7. Lack of security i.e. there are no restrictions on who can see what.
Relational Database
A Relational Database is designed to handle data in TABLE form and a single database is
likely to contain a number of separate but related, tables.
Database management systems (DBMS)
A DBMS is application software that lets you create, organise, update, store, and retrieve data
from a single database or several databases. Lets you transform or map data from one model
to another, or between the central model and stored database
Benefits of DBMS
• Builds the database – creates and maintains the database dictionary.
• Manages the database - has to ensure problems do not arise if two people simultaneously
access a record and try to update it.
• Governs interactions between application programs, input data and the database itself i.e. allows
users to store, retrieve and update as easily as possible without having to be aware of the
internal structure of the database.
• Provides an interface between users and the database
• Backup and Recovery in event of a system failure.
• Security - password allocation and access rights to particular layouts
Data Model
Although a single collection of data it will appear different when viewed from different parts by different users and applications. A data model is the logical structure of the data as it
appears at a particular level of the database system.
Entities
- When a new system is to be designed, the systems analyst will identify the entities (or tables) that exist within it.
- An entity is a person, place or thing on which information is maintained - e.g. employee in a personnel file, Product, Supplier etc.
- Examples of entities in a computerised order system might be customer, order, invoice, delivery note, stock item, supplier etc.
Attributes
- These tables are organised into fields (attributes) which are characteristics of entities.
- ATTRIBUTES e.g. a Personnel database an entity Employee may have attributes employeeCode, Name, JobCode, JobTitle, DepartmentCode etc. These attributes determine the fields in the Employee table. Some attributes may be kept in a different table e.g. JobTitle is likely to have same value in numerous records.
Relationships
- One of the jobs of the system designer is to map the relationships between entities. This helps to ensure that the system will have the necessary data structure and that redundancy will be avoided. Relationships between entities can be one to many, many to many and one to one.
- Records within tables are so organised that they can be accessed in any way, i.e. different entities can be combined in a number of ways because of the relationships established between them.
Types of relationships
In a RDBMS relations are of three types .
One-to-One Relationships
A pair of tables bears a one-to-one relationship when a single record in the first table is related to only one record in the second table, and a single record in the second table is related to only one record in the first table.The following figure shows a generic example of a one-to-one relationship.
As you can see, a single record in TABLE A is related to only one record in TABLE B, and a single record in TABLE B is related to only one record in TABLE A. A one-to-one relationship usually (but not always) involves a subset table. The following figure shows an example of a typical one-to-one relationship that you might find in a database for an organization's human resources department.
The line that appears between the tables in the diagram indicates the type of relationship, and there is a particular line that you use for each type.The following figure shows the relationship diagram for the EMPLOYEES and COMPENSATION tables
One-to-Many Relationships
A one-to-many relationship exists between a pair of tables when a single record in the first table can be related to one or more records in the second table, but a single record in the second table can be related to only one record in the first table. Let's look at a generic example of this type of relationship.
Say you're working with two tables, TABLE A and TABLE B, that have a one-to-many relationship between them. Because of the relationship, a single record in TABLE A can be related to one or more records in TABLE B. The following figure shows the relationship from the perspective of TABLE A.
The following example shows a common example of a one-to-many relationship that you might find in a database for a video rental store.
A customer can check out any number of videos, so a single record in the CUSTOMERS table can be related to one or more records in the CUSTOMER RENTALS table. A single video, however, is associated with only one customer at any given time, so a single record in the CUSTOMER RENTALS table is related to only one record in the CUSTOMERS table.
The following figure shows a generic example of how you create a relationship diagram for a one-to-many relationship.
Many-to-Many Relationships
A pair of tables bears a many-to-many relationship when a single record in the first table can be related to one or more records in the second table and a single record in the second table can be related to one or more records in the first table.
Assume once again that you're working with TABLE A and TABLE B and that there is a many-to-many relationship between them. Because of the relationship, a single record in TABLE A can be related to one or more records (but not necessarily all) in TABLE B. Conversely, a single record in the TABLE B can be related to one or more records (but not necessarily all) in TABLE A. The following figure shows the relationship from the perspective of each table.
A student can attend one or more classes during a school year, so a single record in the STUDENTS table can be related to one or more records in the CLASSES table. Conversely, one or more students will attend a given class, so a single record in the CLASSES table can be related to one or more records in the STUDENTS table.
The following example shows a generic example of how you create a relationship diagram for a many-to-many relationship.
Advantages of using an RDBMS
1. Single centralised store of data for all applications in the organisation that can then be pooled.
2. Independent of application program - many different applications can use data from
common shared database(s)
3. Data is consistent: when an attribute in a table is updated, its up-to-date value is available to
all users of the RDBMS, in whatever report they use and in exactly the same form.
4. Less data redundancy- because there is only one copy of each attribute kept-duplication
should be eliminated altogether in a well-designed RDBMS
5. Ease of reporting/flexibility - easy to set up new relationships and new entities. New tables
and reports can be set up as and when required.
6. Easier security- all access to data is via a centralised system, a uniform system of security
monitoring can be implemented.
NORMALISATION
Database Normalization is organizing non structured data in to structured data.Database normalization is nothing but organizing the tables and columns of the tables in such way that it should reduce the data redundancy and complexity of data and improves the integrity of data.
Purpose of Normalisation
The aim of all well-designed RDBMS is t step-by-step set of rules by which data is put in its simplest forms i.e. distinct entities and attributes. Thus in a fully normalised RDBMS there will Reduced data redundancy i.e. no unnecessarily duplicated data.
Database Normalization is used for following Purpose:
To Eliminate the redundant or useless data
To Reduce the complexity of the data
To Ensure the relationship between tables as well as data in the tables
To Ensure data dependencies and data is logically stored.
Problem in existing system
Example: Student and Course
In a flatfile this would be represented as
STUDENT( Student number, student name, DOB, Sex, course number, course name, lecturer
number, lecturer name)
Because this is a many-to-many relationship many course entries would need to be entered . We
do not want repeating attributes such as course1, course2, course3.
1NF (First Normal Form) No repeating attributes
To be in the first normal form, a table should contain no repeating attributes. This avoids data
inconsistency.
Divide the information into two entities in standard notation, course number becomes part of the
composite key in the student table.
STUDENT( Student number, student name, DOB, Sex, course number)
COURSE (course number , course name, lecturer number, lecturer name)
It contains only automic values.
Automic values:- The Single cell have only single value
2NF (Second Normal Form) Partial key dependence test
For a table to be in the Second Normal Form,
It should be in the First Normal form.
And, it should not have Partial Dependency.
Student name is only dependent on Student number and not on course number)
A third table is needed to link between the two entities.
STUDENT( Student number, student name, DOB, Sex)
STUDENT-TAKES(Student number, course number)
COURSE (course number , course name, lecturer number, lecturer name)
3NF (Third Normal Form) Non-key dependence test
No non-key dependencies are allowed. No field should be dependant on another field (unless the
field is a key field). This prevents any duplication of attributes.
In addition to being in second normal form (2NF), each non-key item in the record is fully
independent of any other non-key data item in the record.
Even though lecturer no. is not a key there are issues here – it is not dependent on course number
which is the key in this table.
We therefore need to create a new relation and table.
A table is said to be in the Third Normal Form when,
- It is in the Second Normal form.
- And, it doesn't have Transitive Dependency.
STUDENT( Student number, student name, DOB, Sex)
STUDENT-TAKES(Student number, course number)
COURSE (course number , course name, lecturer number)
LECTURER(lecturer number, lecturer name)
Boyce and Codd Normal Form (BCNF)
Boyce and Codd Normal Form is a higher version of the Third Normal form. This form deals with certain type of anomaly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following conditions must be satisfied:
R must be in 3rd Normal Form
and, for each functional dependency ( X → Y ), X should be a super Key.
Fourth Normal Form (4NF)
A table is said to be in the Fourth Normal Form when,
- It is in the Boyce-Codd Normal Form.
- And, it doesn't have Multi-Valued Dependency.
0 Comments