Online Databasing And Implementation Computer Science Essay

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

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.

Deeveedeez, 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.

Comment 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, director.

People have attributes of peopleId and peopleName.

dvdDetails

dvdId

title

retailPrice

releaseDate

runningTime

synopsis

genres

studio

users

userId

initials

firstName

surname

dateOfBirth

address

email

mobileNo

telephoneNo

password

timestamp

studio

Fig: Database Design

Add

view

Users

Add

roles

id

peopleId

dvdId

role

wishlist

id

email

userId

wishlist

Customers

view

dvdClassification

id

dvdId

classification

description

people

peopleId

peopleFirstName

review

reviewId

userId

email

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

email

address

password

Implementation:

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.

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.

Table3: review table

Comments:

It stores the reviews and the rating i.e No. Of stars given to the DVD by the user.

It has not null contraints ,check constraints which checks that the rating field have value greater than 0 and less than 5,foreign key contrains.'email ' is the foreign key which is refrencing to the primary key of the users table.

'userId' and 'dvdId' the combination of these two is used as a primary key.

Table4: people table

Comment :

It stores the name of the people involved in the movie or DVD and gives a unique id to each of them that is 'peopleId'.

Table5: role table

Comment:

It stores the role of the each people involved in the movie like director,producer,actor etc.

It has a foreign key 'dvdId' which refrences to the dvdDetails 'dvdId'.'id ' is the primary key which is a identity with a seed 1.

Table6: wishlist table

Comment:

It stores the wishlist of each user in comma seprated form.

In this table 'id' is the identity with seed 1 and userId is the primary key and 'emai'l is the foreign key.

Table7: dvdClassification table

Comment:

It stores the classification of the dvd with a little description about the dvd.

'dvdId' is the foreign key and 'id' primary key and also a identity with seed 1.

Constraints

NOT NULL Constraint:

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.

Or

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.

SAMPLE and SUGGESTED QUERIES:

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 Shahrukh or Amir khan 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

FROM roles AS [NATURAL]

INNER JOIN people ON people.peopleId = [NATURAL].peopleId

WHERE ([NATURAL].role = 'Director') OR (people.peopleFirstName = 'Farah Khan')) AS choose ON choose.dvdId = dvdDetails.dvdId

Table: Result of the query

View of all the DVDs suitable for a Valentines Gift.

Table: Initial data in dvdDetails table

Query:-

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

FROM dvdDetails

WHERE (genres = 'Valentines Gift')

Table: Result of the query

View of all the DVDs suitable for children (under 10s) to watch.

Table: Initial data in dvdDetails table

Query:-

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

FROM dvdDetails

WHERE (genres = 'under 10')

Table: Result of the query

DVDs on the wishlist for a specified customer

Table:Initial data in wishlist table

Query:-

SELECT wishList

FROM wishlist

WHERE (userId = 1)

Table: Result of the query

DVDs in order of release date

Table: Initial data in dvdDetails table

Query:-

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

FROM dvdDetails

ORDER BY releaseDate

Table: Result of the query

DVDs belonging to a specified studio.

Table:Initial data in dvdDetails table

Query:-

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

FROM dvdDetails

ORDER BY releaseDate

Table:Result of the query

To add a sample DVD.

Table: Initial data in dvdDetails table

Query:-

INSERT INTO dvdDetails

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

VALUES (399, CONVERT(DATETIME, '1990-05-02 00:00:00', 102), 'Action', 'Coke Studio', 1100, 'Mard')

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

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 updted 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.

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.

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.