Introduction
This milestone has both theoretical and application components. In the first part, you will provide us the func-
tionaldependenciespresentinalltherelations(ortables)thatyouareusing. Thenyouwillconverttheserelations
intoBCNF(Boyce–Coddnormalform)iftherelationsarenotinBCNFalready.
Forthesecondpart,Youwillworkonmostly HTML and PHP + SQL . Thisisthefirsttimeyouwillshare
yourdatabasewiththewholeworld(oratleastwitheveryoneinUofR).Inthispenultimatemilestone,youwill
transformthelogicaldesignof yourdatabaseintoaphysicaldesign. Youwillpopulateyourtablesandprovide
us‘real’interfacesforinteractingwiththedatabase.
Task A: BCNF Normalization
ArelationalschemaRisinBoyce–CoddnormalformifandonlyifforeveryoneofitsdependenciesX!Y,
atleastoneofthefollowingconditionshold:
• X!Y isatrivialfunctionaldependency(Y X)
• X isasuperkeyforschema/relation/tableR
For this milestone, you need to make sure all of your relations are in Boyce-Codd normal form. Provide
us a list of dependencies for each relation. Decompose them if the tables are not in BCNF. After the
decomposition,alltheresultantrelationsshouldbeinBCNF.
If you decide to keep a particular relation in 3NF instead of BCNF, justify the decision. (Hint: Lossless
and/or Dependency preserving decomposition). Submit TaskA.pdf which contains the details of the
transformation from the initial schema to the final schema where all the relations are in BCNF. This file
should also contain all the functional dependencies you have started with. Note: This is quite possible
that your initial schema is already in BCNF and in that case you just need to provide us the functional
dependenciesandconvinceusthattherelationsarealreadyinBCNF.
Task B: Create forms using HTML
In Milestone 1, you gave us your web-interface as images. This time you will provide these as HTML files.
Provide us a list of HTML files for the interface. These interfaces are mostly used for collecting input from
the users. This task does not require any PHP coding or database connectivity. We are only interested in the
interface. Youmayadditionallywanttoadd css filesforstyling,and javascript. filesforinputvalidation
ifrequired.
• CSS and Javascript. are not mandatory for this milestone. But for the last milestone, your interface
should have a good look and all the entries should be validated. So, it is better to start with CSS and
Javascriptnowthanlater.
• SomeoftheseHTMLfilesmightbeincompleteandthat’sok. Especially,allthefilesthoseyouwant
tobepopulatedfromthedatabasesmightbeempty. Justputanotestatingthatyouwilldosolater.
Project#1(Milestone3) Page1/2
Putallthe html, css, javascript. (ifany)filesintoadirectory taskB .
Task C: Creating and loading relations
In Milestone 2, you have designed the relations (table) those are required for your project. For this milestone,
youwillcreatetheactualrelations. Createafile create.sql whichwillcreateallthetablesinyourdatabase.
load these relations from data files (tab or comma separated files). The tab or comma separated files can be
createdbyyou(dummyvalues)oryoucanprovidethesources. Createa load.sql fileforbulkloading.
Createa readme.txt filewhichstatesthesourceofyourdata.
Put create.sql , load.sql , all the .dat files(or .csv files, or data files in any other format) and
a readme.txt fileintoadirectory taskC .
(Note: create.sql and load.sql filesshouldhavethesamestructureasinProject2Part1)
Task D: Accessing the relations from Web
For Task D, all we need is an address of a web page on Betaweb server. This webpage should contain links to
viewthecontentofeachrelation/tableyoumadeinTaskC.
Forexample,IfyourprojecthadthefourrelationsthatweusedforProject2,youwouldprovideusalinkwhich
resembles:http://betaweb.csug.rochester.edu/~tbiswas2/class_demo/q8/show_relations.html.
Save the address of the web page as taskD.txt . This file should also contain a brief description of
contributionsmadebyeachmember. Wewouldliketoseeanevendistributionofworkload.
Submission instructions
Create a new directory milestone3 . Copy file taskA.pdf , directory taskB , directory taskC , and
taskD.txt to milestone3 . Archive(tar)thefolderas milestone3.tar .
mkdir milestone3
cp taskA.pdf milestone3/
cp -r taskB milestone3/
cp -r taskC milestone3/
cp taskD.txt milestone3/
tar -cvf milestone3.tar milestone3
Submitthetarfileusingthecommand:
~csc261/submit p1m3 milestone3.tar
Youshouldgetafeedbackwhetherthesubmissionissuccessfulornot.
Youmayresubmitasmanytimesasyoulike;however,onlythelatestsubmission(alongwiththetimestamp)
willbesaved,andwewilluseyourlatestsubmissionforgradingyourwork. Submissionsviaemailwillnotbe
accepted! Nolatesubmissionsallowed.
Youmustadheretothenamingguidelinetoavoidlosingpoints.
Project#1(Milestone3) Page2/2