The Process of Decomposition

Added on - 16 Sep 2019

  • 25

    pages

  • 2842

    words

  • 107

    views

  • 0

    downloads

Showing pages 1 to 6 of 25 pages
2. Requirements of the course work2.1 Content PagesThe website will contains the multiple pages for the whole MyLibrary website. The contentpages may be as follows:Home page for allAdmin sign in pageUser sign in pageAdd member pageUpdate member pageView all member pageAdd book pageAdd video(CDs) pageUpdate books / cds pageSee all books / video (CDs) pageDetail page for book/CDsThus these are the whole pages inside the website for the MyLibrary.2.2 NormalizationThe normalization is concept which is used to reduce the complexity from a data base.Normalization is define as the process of the decomposition the redundant relation schemas bybreaking up their attributes into smaller schemas. There are many forms of the normalizationthat are described as follows:1.1stNormal formA relation is said be in 1stNF if and only if every entry of the relation has at most a singlevalue only that is the values in the domain of each attribute of the relation should beatomic.2.2ndNormal formA relation R is in the second normal form if and only if it is in first normal form and everynon key attribute is fully functional dependent on the primary key. To be in second NFthe concept of fully functional dependency should be pre-veiled.3.3rdNormal form3NF is based on the concept of transitive dependency. A functional dependency Xproduces Y in a relation schema R is a transitive dependency if there is a set of attributes
Z that is neither a candidate key and nor a subset of any key of R and both X produces Zand Z produces Y hold.A relation schema R is in 3NF if whenever functional dependency X produces A holds inR either:(a)X is super key of R.(b)A is a prime attribute (If any one property is exist.)There normalization for the tables of the database up to 3rdnormal form is given asfollows:1.LMSADMIN(id, password, firstname, lastname, address, dateofbirth, phonenumber)The primary key is the ID which defines an admin details. There will be oneadmin per id because only one id is recorded for each admin. Since there isno repeating group so relation is in 1 NFThe primary is ID that is the only candidate key since the admin firstnameand lastname is not guaranteed to be unique (two different admin may havethe same firstname and lastname).Address, dateofbirth and phonenumberis also not guaranteed to be unique. This means that ID functionallydetermines every other attribute in the table. There is a single valuedsimple candidate key (ID) and therefore no partial dependencies arepossible. Hence, the relation is in 2NFFirstname and Lastname cannot be used to functionally determine any otherattribute in the table since two different admin may have the samefirstname and lastname. Therefore there are no transitive dependencies inthe table. Therefore it meets the requirements of first, second and thirdnormal form.2.LMSMEMBER(id, password, firstname, lastname, address, dateofbirth,phonenumber)The primary key is the ID which defines an member details. There will beone member per id because only one id is recorded for each member. Sincethere is no repeating group so relation is in 1 NFThe primary is ID that is the only candidate key since the member firstnameand lastname is not guaranteed to be unique (two different admin mayhave the same firstname and lastname).Address, dateofbirth andphonenumber is also not guaranteed to be unique. This means that IDfunctionally determines every other attribute in the table. There is a singlevalued simple candidate key (ID) and therefore no partial dependencies arepossible. Hence, the relation is in 2NF
Firstname and Lastname cannot be used to functionally determine anyother attribute in the table since two differentmembermay have the samefirstname and lastname. Therefore there are no transitive dependencies inthe table. Therefore it meets the requirements of first, second and thirdnormal form.3.LMSASSET(id, discriminator, title, cost,purchasedate, copies, authordirector, genre,pagestime, isbn, imageurl, statusid)The primary key is the ID which defines a book details. There will be onebook per ID because only one ID is recorded for each book. Since there is norepeating group so relation is in 1 NFThe primary is ID that is the only candidate key since the book title is notguaranteed to be unique (two different books may have the same title).Isbn is also not guaranteed to be unique. This means that ID functionallydetermines every other attribute in the table. There is a single valuedsimple candidate key (ID) and therefore no partial dependencies arepossible. Hence, the relation is in 2NFTitle cannot be used to functionally determine any other attribute in thetable since two different Books may have the same title. Therefore there areno transitive dependencies in the table. Therefore it meets therequirements of first, second and third normal form.4.LMSDISCRIMINATOR(id, name, description)The primary key is the ID which defines a discrimination details. There willbe one discrimination per ID because only one ID is recorded for eachdiscrimination. Since there is no repeating group so relation is in 1 NFThe primary is ID that is the only candidate key since the discrimination titleis not guaranteed to be unique (two different discrimination may have thesame name).Description is also not guaranteed to be unique. This meansthat ID functionally determines every other attribute in the table. There is asingle valued simple candidate key (ID) and therefore no partialdependencies are possible. Hence, the relation is in 2NFDescription cannot be used to functionally determine any other attribute inthe table since two differentdiscriminationmay have the same name.Therefore there are no transitive dependencies in the table. Therefore itmeets the requirements of first, second and third normal form.5.LMSSTATUS(id, name, description)
The primary key is the ID which defines a status details. There will be onestatus per ID because only one ID is recorded for each status. Since there isno repeating group so relation is in 1 NFThe primary is ID that is the only candidate key since the status title is notguaranteed to be unique (two different status may have the same name).Description is also not guaranteed to be unique. This means that IDfunctionally determines every other attribute in the table. There is a singlevalued simple candidate key (ID) and therefore no partial dependencies arepossible. Hence, the relation is in 2NFDescription cannot be used to functionally determine any other attribute inthe table since two differentstatusmay have the same name. Thereforethere are no transitive dependencies in the table. Therefore it meets therequirements of first, second and third normal form.6.LMSCHECKOUT(id, assetid, membered, checkedout, checkedin)The primary key is the ID which defines a checkout details. There will be onecheckout per ID because only one ID is recorded for each checkout. Sincethere is no repeating group so relation is in 1 NFAssetid and MemberId are acting as foreign key and has a dependencywhich has relation is in @2NFCheckedOut and CheckedIn cannot be used to functionally determine anyother attribute in the table since two differentcolumsmay have the nothave same datetime. Therefore there are no transitive dependencies in thetable. Therefore it meets the requirements of first, second and third normalform.2.3 E-R ModelThe Entity relationship diagram for the tables is given as follows:
Figure1- ERDThe entity relationship diagram contains six entities which have their particular attributes.2.4 Data DictionaryThe data dictionary for the data base of the Mylibrary website is given as follows:Figure2- LMSADMIN
Figure3- LMSMEMBERFigure4- LMSASSETFigure5- LMSDISCRIMINATOR