XML Database: An Introduction and Analysis

XML, mnemonic of eXtensible Markup Language is one of the most extensively used data format and is used as a standard for exchange of data over the internet. As large amount of data is represented as XML documents, techniques like RDMS have to be used to store and query these XML documents.

There are two well known approaches for parsing an XML document to convert into relational DBMS: SAX and DOM parsing. In this research, both these methods are studied and then the performance is compared. Some alternative ways of data structuring and tagging from tables of RDBMS as a hierarchical XML document has been studied. As a final result, the best alternative which will provide with the best and effective performance for capturing and querying XML data using RDBMS can be found out.

When a large amount of data is to be loaded into an XML document, it becomes important to execute the request query and save the process at XML document. One way is to use XML native data base system. This has two weaknesses –

1. eXtended Markup Language resident database system is not ample to save data and it cannot contain the complicated query at relational DBMS.

2. It is impossible for the users to access XML documents and data that are stored in a relational DBMS.

To overcome the above mentioned weaknesses, querying and storing XML data techniques using RDBMS are used. The steps for this approach are-

1. Data or an XML document is saved by making relational table design.

2. XML data is divided by separating them into columns in the presented table.

3. SQL Queries are used to get access to the XML document format obtained in RDBMS dta format.

To transfer data from XML to data base, the strategy is to use C# as middleware. This research can be done by parsing technique with the help of SAX parser or by using XML Tree Class.

To do the vice versa, the present format of XML document from relational SQL SERVER 2008 database supported by C# script, used as middleware; is converted with the help of tagging and structure. All alternatives (outside engine) done by tagging and structuring process implies that a part of the task was done outside the relational database engine.

For comparing the XML document and RDBMS from the point of speed in loading the process in the browser following tasks were performed:-

Searching of the data in the XML document is done with the help of DATA binding technique.

Presentation of the XML data from RDBMS is done by searching data from XML document which was saved into SQL database, than with the help of DOM Tree method the result of the query is saved as a XML Document

Database problems in XML

They are hard to update.

We cannot implement the constraints.

It’s difficult to optimize the xml database.

Hard to maintain the consistency

Data Storage and Data Transmission:

These two are different concepts. In data storage we store data and while storing it we need to check the validity of the data. In Data Transmission we simple transfer the data from one system to the other. XML are generally used for the Data Transmission purpose.

COMPARISON

Relational Database

XML Database

META DATA Definition

CREATE TABLE

DEFINE XML SCHEMA

Persistence of DATA

INSERT,UPDATE

CREATE XML DOCUMENT

QUERY

SELECT

XPath ,XQuery

1.2 BENEFITS AND LIMITATIONS OF DIFFERENT DATABASE TECHNOLOGIES:

Speed is the major advantage of creating database with objects. In OODBMS (Object oriented Database Management System) data is stored as objects but not in the form relational rows and columns such as in relational DBMS that is why OODBMS is faster than relational DBMS.

OODBMS is more applicable than relational DBMS (RDBMS) for some tasks. This is because OODBMS performs many operations using navigational interfaces where as RDBMS uses declarative interfaces. OODBMS follows pointers to implement navigational access to data in an efficient manner.

One disadvantage of RDBMS ‘s is we need relational mapping layer to map the entire model for application objects to the database object model, whereas in OODBMS there is no need to match those layers. In RDBMS due to this mapping there may be an impedance mismatch and there is no such problem in OODBMS and it also increases the performance.

OODBMS has some drawbacks like:-

As it is said above OODBMS uses pointers to implement navigational access to data this is one of the drawback as in general purpose queries this will lead to delay the output and also becomes complicated than relational

One more drawback is that there is no mathematical foundation for OODBMS, unlike RDBMS. This makes OODBMS weaker than RDBMS in supporting queries.

The difference between data-base centric thinking and oop is in the way it views the world. The first one views the world through a viewpoint which is declarative and attribute-driven. The latter one views through a viewpoint which is behavioural. This is one of the major differences between databases and OOP. Many consider database technology as a failure. Hence attempt are being made in research and industrially to closely integrate database functionality into object programming languages.

1.3 DIFFERENT APPROACHES FOR THE DATABASE DESGIGN

Different approaches for the database design includes data model approach design approach and so on. Starting with the data model approach:

For effective project implementation database design is most important aspect that’s why should be completed within given time line to give economical approach in project developing phase. Data model construction and updating on change of data deign in necessary as every application will need data storage. It is one of the most crucial parts of an application for developer and user as well. The different normal forms are:-

1st Normal Form

Similar group should be stored in different table and tables should be provided by primary key to identify columns.

2nd Normal Form

To protect data redundancy foreign key must be introduced.

3rd Normal Form

Primary key plays a prominent role making every column in the table related. This makes every column a dependent of the primary key of the table. Those fields not satisfying above property should be stored in a new table with a new key.

4th Normal Form:

Independent relationship should be removed from the relation database.

5th Normal Form

Many to many relationships can be related logically that’s why it’s also known as Exists in never-never land.

Followed by the data model approach is the design approach which is explained as below:

2nd and 3rd form designs should be used for large application having enhancement scope. Scalability should be kept in mind during designing phase of application.

Third form can ask you to make host of tables with less number of entity which reduces data replication across tables. While talking to a fellow developer i came to know that they used 3rd form as they were quite sure that it would not create dependency problem for their application as they used comma to differentiate primary values in a defined field of a table.

Classes that can be used as data object which contain properties, actually a description and quality of entity element and these properties are introduced by getter and setter, should be designed at the time of creating class diagram for an application. Both object and property shows real life entity. For creating the database tables we need following constraints and relationships to be there in that table so that final database developed will be more effective and understandable. These are explained in the Constrains section under the database design part with the help of query tools.

LO2 DEsign and implementation:

2.1 Requirement:

This assignment is to understand the database design concepts, implement the database designed and execute the queries to obtain the required result with the help of SQL Server database. It also helps in developing Client side application to modify, update and view the results through C#. Net.

Deeveedeezee, the online DVD shop records the customer details, DVD details which includes title, genres, Studio, classification, actors, directors and other information. It provides the users to rate a DVD using star scaling ranging from 1 to 5 and also allows them to create their own wish list.

Our objective is to understand how the database of this DEEVEEDEEZEE is maintained, practically implementing it with the help of SQL Server and develop an client end application that interacts with SQL Server and provides the user the authority to view and add details.

The WindowsFormApplication1 is the project tile and the client application can be run by opening the solution in MS Visual studio and Debugging the Code. It can be done by clicking on Build->Start Debugging.

Function:

Deeveedeezee.com as mentioned above provides the online customers various details about the DVDs, their reviews, ratings, synopsis and also allows them to create their own wish list. To maintain such a database, it has to be well designed and effectively handled by the administrator or the team of database continuously monitoring and assessing the required information.

Every user is provided with unique user-id and password with which they can login and access the required information about the DVDs. They can even provide their comments, reviews and ratings as well which are again recorded under their user-id and are provided when ever required or asked for. Also deeveedeezee handling such large information from the website requires an Administrator or a team working on databases so that it is well managed and maintained. Thus every administrator and the team member again have got their own user-ids and passwords so that they can login into the databases and do their assigned tasks. These discussed details are presumed to be handled and the rest of the discussion focuses on the database designing, implementation and development of a client application which interacts with SQL server to provide them the easier access to add and view data from the database.

Thus the backend database has got various data rules, definitions and constraints to be handled. Recording distinct information and accessed by different types of users, database has got different tables and views with appropriate fields and attributes. The database design diagram shown below shows the clear picture of how the database is designed, the various tables it maintains, attributes it holds and their properties.

DATABASE DESIGN DIAGRAM & ERD

The diagram shown below depicts the various tables and its attributes. This design is modified and improved further with the help of Entity Relationship Diagram. The Entity Relationship diagram of the report is as below.

The rectangle boxes are considered as entities

The ovals are treated as attributes.

Rhombus represents relationship between the entities.

The diagram represents all entities, attributes and also the relations between the entities.

It is a graphical representation of the relations.

The ER diagram is a good means of understanding the relationships between the entities and attributes.

In the below diagram, different users can be uniquely determined by its attributes like userId, email, firstName etc.

User entity is related to DVD by Reviews.

The ratings given to the movie by viewers are recorded through Ratings.

Comments are the reviews of viewers.

DVD on the other hand can be uniquely determined by its attributes like dvdId, title, studio etc.

Role connects DVD and People.

Role can be various tasks of people like actors, producer, and director.

People have attributes of peopleId and peopleName.

dvdDetails

dvdId

title

retailPrice

releaseDate

runningTime

synopsis

genres

studio

users

userId

initials

firstName

surname

dateOfBirth

address

mobileNo

telephoneNo

password

timestamp

studio

Fig: Database Design

Add

view

Users

classification

description

people

peopleId

peopleFirstName

review

reviewId

userId

dvdId

review

rating

timestamp

Entity Relationship Diagram (ERD)

peopleName

peopleId

people

Role

dvdId

title

retailPrice

releaseDate

runningTime

synopsis

genres

studio

DVD

RatingComment

Review

userId

dateOfBirth

surName

firstName

initials

User

telephoneNo

mobileNo

address

password

2.2 & 4.3 DOCUMENTATION AND Implementation OF THE DATABASE:

Database Implementation and Datatype Usage

The designed database discussed with the help of database design diagram and entity relationship diagram are implemented in SQL with the help of MS visual studio.

All the attribute and tablenames are in used as per the conventions like using camel case, singular table name and so on. The output tables, conents nd query results are recorded with the help of screenshots and are shown as below:

Table1: user table

Comments:

The details of the users like firstName,email, DOB, address, contact details are recoderd.

In this table Primary key contraint,Not Null constraints are present.

Datatypes of each attribute as per the given data rules are being implemented as shown above.

Table2: dvdDetail table

Comments:

It stores the all details of DVD like running time ,release date,title and creates an unique dvd id.

This is a constraint which restricts the user from entering the null value or leaving it blank.

The user needs to provide the details for that field.

It becomes necessary for the user to enter.

Fig: NOT NULL CONSTRAINT

In the given example ‘id’,’email’,’userId’ fields can not be left empty. User need to provide these details otherwise it will through error.’wishlist can be left empty.

Query for this constraint

CREATE TABLE wishList

( id int NOT NULL,

email varchar(100) NOT NULL ,

userId int ,

wishList varchar(MAX) NOT NULL)

Primary key constraints:

This constraint is used to uniquely identify the row in the table.

This field is not repeated in any of the other row.

Primary key of one table can be used as the reference or foreign key of the other table.

Fig: PRIMARY KEY Constraint

In the above example ‘userId’ is the primary key. All the rows in this table will have different userId.

Fig: Showing the implication of Primary Key constraint

In the above figure it is clealy shown that the userId is different for different rows.

Unique constraint:

This is the constraint used for uniquely identifying a particular column.

All the values in a particular column are different.

Fig: UNIQUE CONSTAINT

In the above figure userId field has a unique constraint.It is a identity with a seed of 1.Every time a new row is inserted is automatically increments the value of the userId which ensures that in userId column no value is repeated.

Fig: Showing the implication of Unique constraint

In the above figure in ‘userId’ column no value is repeated . ‘userId’ has a unique constraint.

Foreign key constraint:

This constraint ensures that the database has a refrential integrity.

Foreign key is works on the refrence of the primary key of some other table.

Query:

CREATE TABLE wishList

(id int ,

userId int PRIMARY KEY,

email varchar (100) references users(email)

wishList varchar(MAX)

);

Fig: Foreign Key Constraints

In the above figure ’email’ is the foreign key which refrences the ’email’ i.e the primary key of the users table.

Check constraint:

This constraint makes sure that all values in a column satisfy certain specific criteria.

Query:

CREATE TABLE review

( reviewId int NOT NULL,

userId int NOT NULL,

dvdId int NOT NULL,

review varchar(200) NOT NULL,

rating float CHECK (rating<6 AND rating>0)

timestamp timestamp)

This check constraint helps in validation of ensuring the star rating to be between 0 and 5.

CREATE TABLE dvdDetail

( dvdId int NOT NULL primary key identity(1,1),

retailPrice int NOT NULL,

title varchar NOT NULL,

synopsis varchar NOT NULL,

runningTime int CHECK (runningTime>10 AND rating<5000) NOT NULL

genres varchar NOT NULL

studio varchar NOT NULL)

This check constraint ensures that the running time to be between 10 and 5000.

LO3 Use of Manipulation and Query tools

3.1 Benefits of Manipulation and Query Tools in Database

Manipulation and Query tools in database include those commands and statements that on execution would help in data manipulation. SQL maintains certain standards which includes DML or Data Manipulation Language. The major SQL commands that include in this DML are INSERT, UPDATE, ALTER and so on. These tools or query commands help in modification of tha data and constant updates that have to be made. SQL Server 2008 offers a very good platform that provides access to database and also it provides the query window for executing various commands that include in the Data Manipulation Language. They are very much useful in making it easier for the user to modify the data and update some information. A fine picture of these tools and their functioning can be understood in the sections below:

3.2 Implementation Query tools:

Query for viewing all DVDs belonging to the Romantic Comedy genre in order of price.

Table: Initial data in dvdDetails table

Query:-

SELECT dvdId, title, retailPrice, releaseDate, runningTime, synopsis, genres, studio

FROM dvdDetails

WHERE (genres = ‘Romantic Comedy’)

ORDER BY retailPrice

The query when executed would result into the table as shown below:

Table: Result of the query

Query for viewing all DVDs belonging to the Universal Pictures UK

studio.

Table:Initial data in dvdDetails table

Query:-

SELECT dvdId, title, retailPrice, releaseDate, runningTime, synopsis, genres, studio

FROM dvdDetails

WHERE (studio = ‘Universal Pictures UK Studio’)

Table: Result of the query

Query for viewing all DVDs in which Johnny Depp or De Caprio have acted.

Table: Initial data in dvdDetails table

Query:-

SELECT dvdDetails.dvdId, dvdDetails.title, dvdDetails.retailPrice, dvdDetails.releaseDate, dvdDetails.runningTime, dvdDetails.synopsis, dvdDetails.genres, dvdDetails.studio, choose.dvdId AS Expr1 FROM dvdDetails INNER JOIN (SELECT DISTINCT [NATURAL].dvdId

FROM roles AS [NATURAL] INNER JOIN people ON people.peopleId = [NATURAL].peopleId

WHERE (people.peopleFirstName = ‘Shahrukh’) OR (people.peopleFirstName = ‘Amir khan’))

AS choose ON choose.dvdId = dvdDetails.dvdId

Table: Result of the query

View all the DVDs which Steven Spielberg has directed.

Table:Initial data in dvdDetails table

Query:-

SELECT dvdDetails.dvdId, dvdDetails.title, dvdDetails.retailPrice, dvdDetails.releaseDate, dvdDetails.runningTime, dvdDetails.synopsis, dvdDetails.genres, dvdDetails.studio, choose.dvdId AS Expr1

FROM dvdDetails

INNER JOIN

(SELECT DISTINCT [NATURAL].dvdId

INSERT INTO dvdDetails

(retailPrice, releaseDate, genres, studio, runningTime, title)

VALUES (99, CONVERT(DATETIME, ‘1990-05-02 00:00:00’, 102), ‘under10’, ‘Coke Studio’, 1100, ‘Tom’)

Table: Result of the query

Table:Data in dvdDetails table after executing query

An update query to change the review star rating assigned by a customer to a DVD.

Table: Initial data in review table

Query:-

UPDATE review

SET rating = 3.5

WHERE (userId = 1)

Table:Result of the query

Table:Data in review table after executing query

3.3 EVALUATION OF QUERY TOOLS:

Queries can be simple and complex. For the complex queries we make the stored procedures, views and trigger which we can use later in our code and for the simple query we can directly embed in the code. Stored procedures contain more than one query and the stored procedure is pre compiled method for accessing the data from the database. Queries including select, insert, update etc are written in the procedures. After committing the query if any error occurs then we roll back and ignore the result of that query.

If the correct relationship is present between the tables than only we can get the correct result from the data

LO4 Use, documenting and implementing of Relational Database Management System

4.1 & 4.2 TESTING OF THE RELATIONAL DATABASE AND IT’s IMPLEMENTATION

Queries in the section 3.2 produce the result correctly i.e. the output of different queries are correct and as we know that queries will give the correct output only when the relation between the tables are correct i.e. the relational database is correctly made. Queries in the view section and the also in the client application desired view produce a correct result which shows that the implementation of the relational database is correct.

Client Application:

Microsoft Visual Studio has been used for developing the client application in C#.NET. Our main objective was to provide the user with the facility to add and view the data from the database so that proper updating can be done and relevant information can be provided to the user through the online website of deeveedeezee.com.

The prerequisites for this application are:

MS Visual Studio installed in the computer system with C#.NET and SQL server.

The MDF file or the database has to be loaded into the application through MS Studio by Data-> Add new datasources-> Database.

Once the database is loaded, the SqlConnection string path has to be modified to the path where the database is located.

Every user of this client application is provided with unique username and password through which he can log in into the application. The application only highlights the required and mentioned features in the assignment of VIEW and ADD. The figure below represents the Client Application as it looks after logging in successfully.

afterlogin client app.JPG Fig: Client Application

The user allowed viewing and adding the data, on clicking the respective buttons would be directed to the forms as shown below:

view and add.jpg

Fig: View and Add forms

One can view the details of DVDs, Reviews posted by the customers through online website, Ratings provided by them, their wish lists and the user data.

One can add up details to be displayed about DVD to the customer, roles played by various actors, actress and other working staff for that particular product, rating as per the reviews and rating awarded by the customers and also new users data.

The following sets of figures describe how the data and various details are added to through the client application into the database:

add dvddetails.JPG Fig: Add dvdDetails

On clicking the submit button, in the coding section the query to insert the details of DVD which includes dvd Name or title, Price, Genres, Date of Release of the DVD, Studio and synopsis is executed and the table of dvdDetails is updated as shown above in the query section.

add ratings.JPG

Fig: Add DVD Ratings

Viewing the details of review and rating provided by the customers through online, the user of this client application can rate the dvd accordingly. He has to identify the dvd ID and based on the information he has got he can provide the rating on a scale of 1 to 5. As explained above similarly on clicking the submit button, the query gets exected in the back and the table of concerned gets updated as shown above in the query section.

add roles1.JPG

Fig: Add Role Details

This helps in providing the customer regarding the cast details of the product.

add users.JPG

Fig: Add Users Details

This helps in validating the user by adding them into the database.

Similarly viewing would allow the user with the following output:

Fig: View of dvdDetails

dvdDetails includes title, price, date of relase and such other features that described the dvd. dvdID is uniquely and automatically generated and is considered as the primary key. From the data available in this table dvds can be classified based on their genre and studio as per requirement.

Fig: View of Roles

This table helps in updating the online site with the roles played by various persons involved in making this product. The persons can be identified by their unique ids.

Fig: View of Wishlist

As provided online by the customers their wishlist gets updated in the database ad can be viewed for knowing required information about the user and his preferences for future use.

Fig: View of Users data

Users or customers using deeveedeezee.com online and th staff using this application, all the details are provided in this table from which one can know about their personal details and contact information to be used for various purposes.

Fig: View of Reviews

Based on the data provided in the reviews by online viewers, the users of this application can add the modified rating based on data collected from many sources.

2.3 Application of a range of database tools to enhance user interface

In all the above view forms, GRIDVIEW tool is used to enable the users to view the tables of results concerned as per their requirement. This enhances the look and provides the user a easier and convenient user interface.

Relational database is a set of relations. The relation, which is a 2D table, is the most important element of storage. A relational database includes one or more of these tables which comprises of a distinctive set of rows and columns. It has advantages that support accessibility, association, and flexibility. There are integrity and reliability rules that invoke the full support of entity integrity and the referential integrity in relation database. The entity integrity guarantees that each tuple will have its own uniqueness by assuring null value is not allowed to any other field that is part of the primary key. Primary and foreign keys support and provide the relational database management system a full referential integrity at the database level which avoids inconsistent updates or deletions. Microsoft Access is an example of a true relational database.

Referential Integrity refers to the enforcement among the entities or tables by applying and establishing relationships. It acts as a logical link between entities. Business rules are determined with the help of foreign keys and these rules ensure that the value present in the child table must also act as a primary key value in the parent table. A Foreign key refers to those values which are a resultant from the primary key table.

There are different types of relationships that exist between the entities. They include: one-to-one relationship, one-to-many relationship and many-to-many relationship

Application of relationship is used to create a link between the entities. This link is implemented mostly by foreign key according to it value in parent table is primary and used a foreign in other table.

Implications of the relational model:

Main implications of a relational database management system are:-

Provides the independence between the physical data storage and logical database structure

All the data can be accessed easily.

Provides the flexibility in database design

Reduces redundancy of the data

The RDBMSs are basically used for the implementation of CRUD – Create, Read, Update, and Delete – functionality.

The RDBMS implementation, testing and its documentation are well explained in the previous section with the help of SQL Server 2008 creating the database for the deeveedeezee.

4.4 Verification and validation in database

Verification in database refers to the process in which the data is checked properly so that it is entered correctly into the database without any errors of transcription. When data gets transferred from one table to other or manually when one transcribes data in one format to other, there might be some possibilities of errors while entering. Such errors have to be verified and are taken care in this project with help of unique and primary keys attached to it. While data is transferred to one table to other, proper and correct data is transcribed and is verified with the help of unique keys attached to them. They are shown with the help of examples in the previous section.

Similarly, validation is the process where we check the sensibility of data. There are various data validation techniques and is dependent on the data item. In the client application various fields and data are validated by providing the constraints so that only valid data can be entered. Rules mentioned in the assignment are well followed and valid data is taken and entered into the database appropriately. This also can be witnessed in the client application section of the report.

4.5 Control Mechanism

Control Mechanism refers to the processes, methods and concepts that help in controlling various issues that occur during data transactions. The control mechanism discussed below is not restricted to only Database Management systems but can also be used for all those systems that face issues with database transactions. For example we can consider concurrency control issues. These are not just applicable in databases but also are the problems faced even in operating systems as well.

Concurrency control refers to the mechanism where data integrity is maintained while ensuring concurrent performance of database transactions used in Database management systems, distributed computing applications, and other transactional objects. It acts as a primary and most essential control mechanism for maintaining the data integrity and to avoid misinterpretations of data. Serializability theory articulates effective designing and analyzing of concurrency control mechanisms in database management systems.

Various properties have to be provided to the database transactions so that data integrity and correctness of data is maintained. These properties include serializability, recoverability, rolling back and so on. In general, database management system ensures correctness and data integrity by generating only serializable data transaction schedules unless it is intentionally stopped for effective performance. In order to ensure the same for failed or aborted transactions the property of recoverability is used. ACID properties are used by the database management system for maintaining the reliability of data and transactions. ACID stands for atomocity, consistency, isolation and durability.

Why?

There are several issues why control mechanism and concurrency control methods have to be implemented. Few of them include the lost update problem, dirty read problem, incorrect summary problem and so on. Thus to avoid such normal and possible occurrences and to maintain data reliability and consistency concurrency control mechanism are to be used. There are three main types of Concurrency control mechanisms. They include: Optimistic, Pessimistic and Semi optimistic mechanisms.

Optimistic – this approach assumes that no serial updates would be done and proceeds with all operations but if it finds any evidence of conflict or concurrency then it discards the user updates and informs the user the same.

Pessimistic – in this approach, it assumes there might be other user updating the process or data at the same time and thus locks while performing any operation on a data during a transaction so that no other operation can do updates; thus maintaining data consistency.

Semi-optimistic – this is more optimal approach where it identifies the type of operation and permits it to proceed optimistic way or else locks the operation as in pessimistic approach.

Two phase locking method, Serialization graph checking, timestamp ordering, commitment ordering, multi version concurrency control and index concurrency control are the various types of concurrency control mechanisms used in the database management systems.

Conclusion:

Thus, understanding various concepts of database and client applications, with the help of deeveedeezee.com; the knowledge behind these concepts are successfully applied and database has been designed as per the requirements and rules, implemented in SQL Server, queries have been executed and the results have been observed and reported. Also a client application interacting with the SQL server is designed for the user to view and update data from the database. In this way, all the given tasks have been successfully completed and are submitted with proper documentation.

Order Now