The Limitation and Strength of the Database - Research Paper Example

Summary Identification of entities, attributes and their relationship are essential in building a good database. This wills help in building the conceptual model diagram. Then it can be modified to be in the logical form after which normalizing of tables is required. . Subsequently the queries have to be done in SQL for the various tasks. In the present case, the limitation and strength of the database have been clearly identified. Table of Contents 1. Summary............... 1 2. Database Management ............................................... 3 3. ER model: Conceptual and Logical models . 12 3.1 Identified entities and their attributes. 12 3.2 ER Diagram ... 14 4. Normalized ER Model to 3NF 15 5. SQL Queries 17 6. Conclusions .. 20 7. References 21 Database Management: Database Management System (DBMS) is an interface between the user and the database. Insert, delete, update, retrieve are the various operations in the database. Fields, records, files and objects form the data structure, which deal with large volume of data. DBMS is a set of programs for creating, maintaining and using the database, which is a collection of data records and files. DBMS uses various models including relational model. DBMS utilizes query language to get information. A query language can analyze and update data. It takes care of the security of the database. Unauthorized users are prevented from viewing, updating etc of the database. Modeling language, query language, data structure and transaction mechanism are the main parts of DBMS.With the help of 'Data Manipulation' subsystem the user can add, delete or change information in a database. Relational Databases are so popular in that they are the absolute choice for storage of financial records, logistical information, manufacturing in formation, personal information etc. The software is Relational Database Management System. (RDMS) Constraint restricts the domain of an attribute and also the data to be stored in relations. . SQL makes use of 'check constraints'. There are also domain constraints as every attribute has a related domain. , There are referential integrity and entity integrity in relational model Normalization is an integral part of relational model. It checks duplication of data and simultaneously loss of data integrity. The Relational Model has three parts. Structures are objects which store the data. Operations manipulate the structure and the data it contains. They follow a set of rules. Integrity rules decide the operations on the data and structure. In fact it protects both. Data has to remain the same during all the operations. It should not get disturbed during transfer, storage and retrieval. Data Integrity takes care that the data will always be consistent, correct and accessible. It remains faithful to its source. However in the internet, the data is broken into packets, before being transferred. Data integrity ensures that, even then the data is put back to shape and recovered. Data integrity can be disturbed by malfunctioning of hardware, attack of software applications by viruses. Human error in data entry is another cause. Referential integrity is the most common. In this, the errors in the relationship between a foreign key and primary key are avoided. Proper use of integrity constraints will prevent end user from writing invalid matters. Also unauthorized persons cannot alter the data. 'Server enforced data integrity' is the most dependable data integrity. RDBMS is considered the authority to accept or reject data. Normalize: It is advisable to normalize the database while designing a relational database. Normalizing to 'Third Normal Form' is always recommended. It means, the attributes in each table should depend on the key. Normalization takes care that errors in data input at are reduced. It removes excess data from records. Data model is an integrated collection of concepts for describing relationship between various data, constraints on the data and manipulating the data. The three components of the data model are: 1. Structure, which is for constructing database. 2. Manipulation, which define the types of operation on the data, like changing the structure of database, updating or retrieving data from the database.3. Integrity, which ensures that the data is correct. The physical model describes the data at the internal level, whereas object based and record based data models do so at conceptual and external levels. . DBMS is a software package which enables the user to access and modify information on the database. In database the changes made are to be reflected in all tables containing the particular field. This is known as' Propagating updates' and is done automatically by centralizing the database. Once centralized, many would be using the data at the same time. This makes it necessary to introduce 'integrity constraints'. Even after centralizing there may be still incorrect data. This problem can be solved by defining the validation procedures whenever an update is made. Standardizing stored data format is indeed worthy when the question of migration between systems or data interchanges. RDBMS is based on relational model. It is the basis for SQL. Tables are database objects which store data. It consists of columns and rows. The table is split into fields. It is a column that gives the specific information about the records in the table. A record and row are one and the same. It is a horizontal entity. The column is vertical entity. The data and relations are organized in tables. A table is a collection of records. Each record contains the same fields. In relational tables, each row is unique and there is unique name for each column. Every column has same kind of value, which are atomic. Some fields are assigned as keys. When fields of different tables take values from the same place, a join operation is done to select the related records. At times multiple tables may contain multiple fields with same name. A null value in a field in a table means no value. A Null value is not the same as zero value. If a field has null value it only means that the field has been unfilled during a record session. The rules applicable to data columns on a table are known as SQL Constraints. They ensure reliability and accuracy of the data. They also limit the type of data entered in the table. Table level constraints are for the whole table, but the column level constraints are meant only for one column. Normally used constraints are Not Null Constraint, Unique Constraint, Default Constraint, Check Constraint, Primary Key, Foreign Key, and Index. All of them have specific functions. RDBMS has different categories of data integrity. They are Entity Integrity, Domain Integrity, User-defined Integrity and Referential Integrity. Database normalization ensures efficient data organization. It takes care to eliminate unnecessary storage of duplicate or unnecessary data in tables. In other words it eliminates the amount of space wasted. It takes care that the data is logically stored. A Form is the manner in which a database structure is made. Normalization forms organize the database structure such that it compiles initially with the first normal form, namely, 1 NF, then the second, namely 2NF and then the third, namely 3NF. Structured Query language can access and manipulate data. It is the basis for SQL. It can create new databases, new tables, stored procedures and views in databases. It can set permissions on tables, procedures and views. It can update, insert or delete records in a database. It can also retrieve data. However, the most important function is that it can execute queries against a database.SQL is being made use of everywhere for easy interactions between users and administrators. The commands are in two sublanguages namely Data Definition language and Data manipulation Language. The DDL commands are used for creating and destroying database and database objects. After defining the database structure, DDL, can be made use of for inserting, retrieving and modifying the data. SQL are very popular with administrators and developers because of its efficiency. SQL is in fact the building block of database architecture. Conceptual, logical, physical are terms normally used in modeling to distinguish levels of abstraction against the details in the model. Data modelers are aware of the scope and limitation of each term. A conceptual entity-relationship model subdues non critical details, but gives importance to business rules and user objects. It takes into consideration business related entities and their relationship. Normally Many-to-many relationship is accepted. It includes attributes, support the definition of entities. It may have some identifying concepts or candidate keys but it does not have a scheme of identity, since identifiers are logical choices. A logical entity- relationship can be proved mathematically. Logical models are as per rational theory. So it has fully normalized entities. For normalizing logical data model, it must consist of all the attributes, which must be defined with respect to their logical data type like, character, number, date etc. For identifying each occurrence of an entity, logical data model need candidate keys or scheme of identifiers. The entities have choice of identifiers. So the logical model shows the identity selected currently. Relational storage does not support Many-to- many concept. But logical data model resolves all such relationship into 'associative entities' which require separate identifiers and even other attributes. .The conceptual model is about the real world view. The logical model is a general formal structure. The physical model shows the way of execution in various Database management systems. Entity Relationship Model: The success of any organization relies on the efficient flow and processing of information. An Entity - Relationship diagram exhibits the relationships among entities in a database. The information is represented by symbols. Entities are represented by Boxes, Attributes by Ovals and Relationships by Diamonds. An entity is a concept or object, whose information need be stored. An Attribute refers to a characteristic or property of an entity. A key attribute is a unique characteristic of the entity. A multi-valued attribute has two or more values. A derived attribute depends on another attribute. A relationship show the way two entities share information. Cardinality indicates the number of instances of an entity with respect to another entity. Cardinality and Ordinality are somewhat related. Ordinality shows optional or compulsory relationships. That is to say, whereas cardinality specifies the maximum number of relationships, Ordinality shows the minimum number of relationships. Data modeling optimizes the way the information is stored in any establishment. It identifies the main data groups and then defines the contents of each group. This help in establishing structured definitions for all information stored in the system. The 'soft box entity symbol' represents entity, about which data is stored in the system. Relationship constitutes the other important component which unites the two entities. It is impossible to define an entity precisely. They vary in nature. Normally they refer to physical objects. Data is stored in the system about the entities. Entities are always named singular. A group of entities is known as Entity Type. It is necessary to know clearly the difference between entities and attribute. The elements of the data group associated with entity are known as attribute. It is ideal to prefix entity names with qualifiers. Entities are usually nouns. Among entities there are potential entities. A fact that is necessary to support the statement is an attribute. Entity occurrence can be identified with the help of attribute or combination of attributes as a key. A relationship is a connection between two entities. A compound key comes in the picture when two or more data items are used as unique identifier. A simple example is using the compound key of Author and title to identify a book. Primary keys are also called candidate key. In foreign key an attribute of one entity is a candidate key of another entity. A data model diagram constitutes the entities and the linking relationship lines. The relationship line shows a couple of reciprocal relationships. That is the relationship of the first entity with respect to the second entity and vice versa. For example the manager handles the establishment. Conversely the establishment is under the control of the manager. The different properties of the relationship line are: 1. Relationship link phrase: This gives a small description of the nature of the relationship in five or six words. 2. Cardinality of the relationship: A crow's foot symbol against an entity means many occurrences of the entity with respect to the other entity. If no crow's foot is shown, that means only one occurrence of entity with respect to the other. For example, in a class there are one teacher and 20 students. This is known as one- to-many relationships. If there is only one teacher and one student then it becomes one-to-one relationship. If there is more than one teacher for the 20 students, then this is called many-to-many relationship. The details above can be summed in the following way: Boxes: Entities: Things: Noun. Diamond: Relationship: Structure: Verb. Ovals: Attributes: Data (Data is answer to queries). Entity: Object: Concept about which information is required. Attribute: Properties or characteristics of an entity. Relationship show how two entities share information in a structure. The final property of the relation statement is the Optionality. The solid line in the entity- relationship diagram indicates that the occurrence of an entity must have relationship with each occurrence of the other entity. For example, every driver must have driving license, and every driving license must belong to a particular driver. A dotted line specifies that the occurrence of an entity is in relation with each occurrence of the other entity. For example, a pencil can be bought by a student, and each student can buy one or more pencils. The only two components of the data model diagram are entities and relationship. All the occurrences of the two entities must confirm to the relationship. The symbols can be combined. The properties of the relationship like Relation Link Phrase, Degree of cardinality of the relationship and Optionality of the relationship put together make the Relationship Statement. Conceptual Model refers to the concepts which deal with a database structure and retrieval and updating transactions associated e with it. This is inevitable in the design of a good database. Entity Relationship Model is based on entities, which are a set of objects and relationships among entity sets which constitute a group of similar objects. The relationship between entity sets is of 1:1, or 1: N or M: N type. ER model: Conceptual and Logical models Identified entities and their attributes 'An entity is something that has a distinct, separate existence, though it need not be a material existence. In particular, abstractions and legal fictions are usually regarded as entities. In general, there is also no presumption that an entity is animate. Entities are used in system developmental models that display communications and internal processing of, say, documents compared to order processing 'Attribute is a specification that defines a property of an object, element, or file. An attribute of an object usually consists of a name and a value; of an element, a type or class name; of a file, a name and extension. A property inherent in a database entity or associated with that entity for database purposes Staff The details of staffs are included in this entity. The main attributes in this entity are. Staff_ID, Staff_Name, Staff_Address, Gender,Position, Salary, Dob,Doj Contact No Customer This entitity includes the details of custmers The main attributes are Cust_ID, Cust_name,Cust_Address ,Status,Category,Amountpaid,Gender Vehicles This entity includes the details of vehicles .The main attributes in this entity are Veh_Id, Staff_Id,PhoneNo,Type Booking This entity includes the booking details of cutomers .The main attributes identified are BKid, Cid, PakId,VehId, DatenadTime, Remarks VehicleStatus This is the entity which is depends on the boking.The main attributes identified are VehID,Time and Date,BkId,SeatId,Status Invoice This holds the details of amount paid by the customer .The main attributes identified are InvNo,BkId,Amount_Paid,Balance,Dateof_Payment,Payment_type Package This entity describes the package details .the main attributes in this entity are PakId,Pakdetails,Amount ER Diagram In this ER Diagram the arrow represents the one side and linen without arrow represents the many side .It contains the mappings like one to one ,One to Many, Many to many. Normalized ER Model to 3 NF TBl Booking Customer Invoice Package Seat Creation Staff Vehicle VehStatus SQL Queries 1. Displaying the details of staffs SELECT * FROM Staff 2. Displaying the vehicles along with allocated staffs SELECT Vehicle.VehId, Vehicle.Type, Vehicle.StaffId, Staff.Staff_Name FROM Staff INNER JOIN Vehicle ON Staff.Staff_ID = Vehicle.StaffId List of Customers SELECT Cust_ID, Cust_name, DoB, Cust_Address, Category, PhoneNo, AmountPaid FROM Customer List of Vehicles , seats in a particular journey SELECT Booking.BKid, Customer.Cust_name, Vehiclestatus.VehID, Vehiclestatus.SeatId, Package.PackDetails, Vehiclestatus.Time_date FROM Booking INNER JOIN Customer ON Booking.Cid = Customer.Cust_ID INNER JOIN Vehiclestatus ON Booking.BKid = Vehiclestatus.BKId INNER JOIN Package ON Booking.PaKId = Package.PackId WHERE (Vehiclestatus.Time_date = CONVERT(DATETIME, '2010-05-02 09:00:00', 102)) List of unpaid invoices SELECT Invoice.InvNo, Invoice.Amount_Paid, Booking.VehId, Booking.DateandTime, Customer.Cust_name, Customer.Cust_Address, Customer.PhoneNo FROM Invoice INNER JOIN Booking ON Invoice.BKid = Booking.BKid INNER JOIN Customer ON Booking.Cid = Customer.Cust_ID WHERE (Invoice.Amount_Paid = 0) SELECT Cust_ID, Cust_name, DoB, Cust_Address, Category, PhoneNo, AmountPaid FROM Customer Details of Booked Customers SELECT Booking.BKid, Booking.VehId, Booking.DateandTime, Customer.Cust_name, Package.PackDetails, Invoice.Amount_Paid, Invoice.Balance, Invoice.dateof_Payment FROM Booking INNER JOIN Customer ON Booking.Cid = Customer.Cust_ID INNER JOIN Package ON Booking.PaKId = Package.PackId INNER JOIN Invoice ON Booking.BKid = Invoice.BKid List of seats free in a Particular Bus journey SELECT SeatCreation.SeatNo, Vehiclestatus.VehID, Package.PackDetails, Booking.DateandTime FROM Booking INNER JOIN Vehiclestatus ON Booking.BKid = Vehiclestatus.BKId INNER JOIN SeatCreation ON Vehiclestatus.VehID = SeatCreation.VehID AND Vehiclestatus.SeatId SeatCreation.SeatNo AND Vehiclestatus.VehID = SeatCreation.VehID INNER JOIN Package ON Booking.PaKId = Package.PackId Conclusions In the report about the database, the identification of entities attributes and their relationship is clearly demonstrated which are essential for the construction of conceptual and logical model. Subsequently tables for the database are formed, where the designer stores information. Then the user can put forth his request with the help of SQL queries. References "Data Integrity", http://www.geekinterview.com/kb/Data-Integrity.html, 2005. web.26.Jan.2010 "Relational Model",http://www.unixspace.com/context/databases.html, web.26.Jan.2010 Arthur, Fuller, Harkins , Sales, Susan , "Logical model" http://articles.techrepublic.com.com/5100-10878_11-1050672.html, Aug 07, 2002, web.26.Jan.2010. ZAMG, " Conceptual Model" www.zamg.ac.at/docu/Manual/SatManu/Basic/CM/CM.htm, web.26.Jan.2010. Sauter, Vicki L, "Entity Relationship diagram" mon,14,aug,2000 , web.26.Jan.2010, http://www.umsl.edu/sauterv/analysis/er/er_intro.html "SQL RDBS Concepts"http://www.tutorialspoint.com/sql/sql-rdbms-concepts.htm, web.26.Jan.2010. Read More