Download Problem Set 4 - Introduction to Database Systems | COMPSCI 186 and more Assignments Computer Science in PDF only on Docsity!
cs186 Ringleaders
- CS186 - HW - Due: Monday, April 24, Groups: 1-2 people.
- 1 NOTICE! Contents
- 2 Introduction
- 2.1 Assignment Overview
- 2.2 Going Deeper - Adding a Restaurant
- 2.2.1 page newrestaurant.php
- 2.2.2 form newrestaurant.php
- 2.2.3 submit newrestaurant.php
- 2.2.4 exec newrestaurant.php
- 2.3 Grading scheme
- 3 Task 1 - Relational Schema Design [20%]
- 3.1 Sample XML file
- 3.2 IAmHungry Functionality in the relational schema
- 3.3 Relational Schema constraints
- 3.4 How will we test the integrity constraints?
- 4 Task 2 - Populate the Database [15%]
- 4.1 How to implement?
- 4.2 PHP Array Tree data structure
- 4.3 How to run importXML?
- 5 Task 3 - Implement IAmHungry Functionality [55%]
- 6 Task 4 - Be Creative [10%]
- 7 Task 42 - word problems [0%]
- 8 What To Submit
- 9 Hints
- 10 Appendix A - Project background
- 10.1 Setting up the Environment
- 10.2 What do we provide in the PHP framework?
- 10.3 Technology Background
- 10.4 Array to XML
- 10.5 Transactions
- 11 Appendix B - Resources
- 11.1 PHP
- 11.2 PostgreSQL
- 11.3 XML, XSLT, HTML
1 NOTICE!
This assignment uses the google maps api for some of its functionality. In order to use the google maps api, you require an api key. Once you obtain the key, open the file ˜include/header.inc and find the section that contains google maps keys. Comment out the existing key (using ) and add your own. More specifically, between the tags you should have something like the following:
The key is obtainable at:
http://www.google.com/apis/maps/signup.html
the website URL should be the URL you will be using to access the website. For example if you are using pentagon, then it would be:
http://pentagon.cs.berkeley.edu/˜cs186-XX/
where you replace XX with your login.
Figure 2: IAmHungry screenshot
2.2 Going Deeper - Adding a Restaurant
The adding restaurant functionality is implemented in the following files:
- page newrestaurant.php
- form newrestaurant.php
- submit newrestaurant.php
- exec newrestaurant.php
2.2.1 page newrestaurant.php
This file displays the input form to the client. You will notice three sections in the layout of the page (figure 2):
(1) The header is located at the top of the page and contains a link menu, a big “IAmHungry” link, and a non-functional search box. The template for displaying this can be found in include/header.inc.
(2) The google map sidebar is located on the left side of the page. It serves to tell the world where the exciting new restaurant is located. It is done in two ways: 1) simply double click on the map to select a location or 2) input an address or cross streets into the location input box. An ajax call will be made to find the geographic location of the address. If successful, the map will center and zoom in on the location. You can try it out by typing “2366 Telegraph Ave” in the location input box. The map defaults to bancroft and telegraph.
(3) The main input form consists of the rest of the input form. It is an included file from form newrestaurant.php
2.2.2 form newrestaurant.php
This is the html for the form displayed on page newrestaurant.php. When the form is submitted, the data is sent via POST to submit newrestaurant.php
2.2.3 submit newrestaurant.php
This php page is a wrapper for exec newrestaurant.php. It populates the input array $rest rec with the values from the form. Then it calls exec newrestaurant.php. When exec newrestaurant.php returns, it checks whether the database call was successful by reading the $successful variable and ensuring that the new restaurant has a valid restaurant id ($rid). Finally, it redirects to the appropriate page.
2.2.4 exec newrestaurant.php
This is the file you will be modifying. It currently ignores the values in the array $rest rec and simply returns with $successful set to false. You will need to replace the existing code with code that adds the restaurant to the database. If multiple commands need to run in the same transaction (e.g., if you want to modify
two different tables at once) you should to surround the commands with “BEGIN TRANSACTION;” and “ABORT;/COMMIT;”. Appendix A has more details about transactions.
2.3 Grading scheme
The assignment consists of the following parts (with percentage of project grade shown):
Task 1 (20%) Design your relational schema. Task 2 (15%) Load the initial data from XML. Task 3 (55%) Implement the application functionality in PHP and SQL. Task 4 (10%) Design and implement a new feature for the site. Task 42 (0%) Project Survey.
Though we have divided the project into multiple tasks, you should submit them together after completing all the tasks.
3 Task 1 - Relational Schema Design [20%]
Once you have set up your environment, your first task is to define and implement a relational schema for the IAmHungry system. We provide a set of initial data which you will use to populate your database. In order to give you the opportunity to design your own relational schema, we provide this data in XML, in the file sample.xml. You can use this file to test your schema.
3.1 Sample XML file
Let us take a look at a small sample XML file
capundies snap captain underpants [email protected]
Naan n Curry 2366 Telegraph Ave -122.259093 37867436
indian Delicious
Cafe Intermezzo 2442 Telegraph Avenue -122.25882 37.866202
coffee tea
- Non-Null Constraints
- The user’s login, password and email
- A restaurant’s name, hours, and (longitude, latitude) coordinates
- A review’s reviewer and restaurant
- A review must have either comments, rating, or both present
- A label may not be null
- All fields (except address) that are required to be unique must also be non-null
- Referential Constraints
- A review’s user should refer to an existing user
- A review’s restaurantname should refer to an existing restaurant’s name
- A label should be attached to one or more existing restaurants
- Other Constraints (Must be implemented using CHECK)
- An email should be of the form [email protected] XXX is a non-null alphanumeric string (upper and lower case) YYY is a non-null alphabetical string (upper and lower caes)
- Restaurant starting hours must be earlier than the closing hours (of course this doesn’t allow for 24/7 restaurants)
- Restaurant’s overall rating is the sum of all user’s ratings for the particular restaurant
- A review’s rating is either -1, 1, or 0
- All strings should not have leading or trailing whitespace
For this assignment, we require that you implement all the above consistency checks in the data- base schema, not the PHP code. We also strongly recommend that your schema be in at least Third Normal Form (3NF) (see textbook chapter 19). You should add your DDL commands to the file schemas/tables.sql.
3.4 How will we test the integrity constraints?
The sample XML data file that we provide meets all the integrity constraints described above. However, when we grade we would want to test your code using different XML data files that may violate some of the constraints that we have specified. To do so, we will need to reset the relational schema by calling schemas/tables.sql. Hence it is essential for you to do clean up by first dropping all of your tables, indices, sequences, etc at the beginning of tables.sql and then create them fresh. After testing some of the integrity constraints this way, we will import a proper XML data file, like the one we have provided and do the remaining tests.
The first time you run tables.sql, obviously it will throw errors on the initial drop commands. But, you can ignore them. Hopefully, you may find implementing tables.sql this way useful while testing. As an example, in the tables.sql file, we have provided SQL for dropping and creating a dummy test table called HungryTest. This table is not used anywhere. It just demonstrates what you need to do in tables.sql. Especially if you are using the SERIAL data type, be sure to drop the sequence it creates as well (see PostgreSQL documentation).
4 Task 2 - Populate the Database [15%]
4.1 How to implement?
Once you have defined your schema in schemas/tables.sql, the next step is to implement the PHP program that enables you to load the initial data into your database. As stated above, the initial data set is represented as XML in the sample.xml file. The program to import the data should be written in the file importXML.php. In the importXML.php file we have already written the code that parses the XML file and converts the XML into a PHP array tree data structure. This PHP array tree data structure is the main vehicle that you would use in passing parameters and results around. You then extract the data from this structure and generate SQL INSERT statements to insert it into your database according to the relational schema you created in the previous step. You should run all of your INSERT statements in one transaction, as outlined in importXML documentation, as well as in PHP documentation. Note that getting this to work should be a top priority for you, as without the importXML.php file, we have no way of grading the rest of the project (i.e., for partial credit if necessary).
4.2 PHP Array Tree data structure
We have implemented an XML parser on top of the SAX parser provided by PHP, in the file include/xmlparse.php file. Our XML parser parses the XML data and converts it into a PHP array tree. XML is essentially a tree structure. If you look at the importXML file, it calls our parser as follows:
$data = new ParseDB($x); $user_recs = $data->user_recs; $rest_recs = $data->rest_recs; $review_recs = $data->review_recs;
Each of $user recs, $rest recs, $review recs are PHP array trees, storing the users, restaurants, and reviews respectively. All of these structures are trees, mapping the XML tree into PHP array of arrays. To access the tree, you should do something like:
$user_recs[0][’login’] // login of first user record
To check if a street address is given or not, you can do:
// get a restaurant array for the first restaurant $address = $rest_recs[0]; if(array_key_exists(’address’, $address)) { // do something }
For information about translating arrays back to XML, please refer to Appendix A.
4.3 How to run importXML?
Now that you understand PHP array tree structures and are ready to implement importXML.php. In order to run the script, you should do it by going to the following URL in your browser:
http://pentagon.cs.berkeley.edu/˜cs186-XX/hw4/importXML.php?filename=sample.xml (where you should use login instead of XX)
This will invoke importXML and feed it with the sample.xml file.
Note that if you cannot insert the initial data into your database, due to type mismatches, string length prob- lems, etc., then you need to go back and change your database schema. You are not allowed to change the initial data (due to grading purposes). Once you have the basic functionality working, you will need to refine your PHP code by adding error handling.
If you have successfully completed this part of the project, you get pretty much everything you need to know about PHP. And you can start your application development!
5 Task 3 - Implement IAmHungry Functionality [55%]
By this point, you should have a database loaded and ready to go, so you are ready to implement the function- ality of IAmHungry!
Excitement Ensues!
The functions you must implement (and the files that contain them) are the following (each of the files contains ample documentation about their input and output parameters):
- exec auth.php: Input: login, password Output: login, user id Authenticate the user, given login and password.
- Support fancy menus
- Allow reservations
- Support directions from current location to restaurant
- More creative user interfaces
- An interface for restaurant owners
- Export web service apis
- The possiblities are limitless!
6.1 Task 4 Grading
Although you are given free reign to design a feature, you must ensure that the final code that you submit still meets the specifications in task 1-3.
Obviously since this is a creative exercise it will be difficult to evaluate your feature objectively.
Before you implement your feature, you must review your design with Eugene either in person (At office hours) or through email ([email protected]) by Weds April 19th. When presenting the design, provide the following items
- Your names and logins
- brief description of the feature
- list files to be modified/added
- estimated time to develop Please note that Task 4 will not be graded unless your design is checked off.
7 Task 42 - word problems [0%]
The following are questions about this project. Although it is not worth any credit (maybe a treat of some sort) we are extremely interested in your thoughts. You answers will help us gauge the scope and difficulty of the project so that we can improve it for future semesters.
Your answers may be any length, but please keep in mind that we may read 40+ surveys!
- Briefy describe the tradeoffs made regarding your schema design
- Please describe what you enjoyed about the project
- Please describe some points that could be improved for the project
- How would you rate the difficulty of the project? Construct your own rating scale. 1-5 and 1-10 scales are not permitted.
- Do you think this project has taught you any practical skills employable in the ’real’ world?
- Anything else?
8 What To Submit
You should submit (at least) the following files. You may also submit other files for function definitions, etc...
Relational Schema schemas/tables.sql - this file should contain the ddls for creating your schema
PHP Files exec *.php - these files must be submitted in order to grade the project any additional files for task 4 README readme.txt - this defines your task 4 and how to use it. You can change dbconn.php only for the setup parameters, as described in section 2.1, but you don’t have to submit it. (And you can add additional files if you need).
9 Hints
Here are some additional hints that will hopefully help you out.
If you create some new file, remember to set the permission of that file to 755 in order for the web server to access it.
Modularity of design and code reuse As you write the code, you will find that all the files you are writing are more or less the same. At this point, you can either do it naively by letting each person write two files separately, or you can try some design that is more modular, and reuse most of your code. Be careful about copying and pasting source code though, since a bug caused by this is really hard to find.
Get started early, and don’t get frustrated! In this assignment, you are expected to write a large number of lines of code (this is the nature of this kind of program). But you will see the code very easy to write compared to a C program. Besides writing the code to compute a personal network, there is no hard coding.
Read the documentation! PHP provides a large number of handy functions, especially for array and string operations. Using them saves you a significant amount of coding. Take a look at the function var dump(), which we found extremely useful for debugging.
Don’t get nervous about specification details All the requirements have been stated explicitly in this write up. Anything we don’t say here means we don’t care. Don’t trouble yourself by details like if you need to put a space in cities or not. It is our task to make the auto grading script perfect, you only need to follow what is specified here.
Read the newsgroup and the blog Usually people get similar questions about the assignment, so we try to answer them in the newsgroup. We will also put up an FAQ page, summarizing common questions. Please check those resources before asking questions.
HAVE FUN! This assignment is meant to be more of a fun experience than anything else. We included
technology such as google maps and ajax for you to have a good time with real world technology. So have fun working on this project, we sure did while designing it!
Figure 3: System architecture
Note: Throughout the project, you must access your web page from pentagon.cs.berkeley.edu. It is not guaranteed to work on any other servers.
Note: since we do not address security in this project, so you should avoid entering any personal or private data about anyone as we have no good way of preserving privacy.
10.2 What do we provide in the PHP framework?
The PHP framework that we provide can be broadly divided into the following:
- exec xxx.php (You should modify ONLY these files for Task 3!) The submit xxx.php files setup the environment in order to call the exec xxx.php files. all database interactions are performed in exec xxx.php files. This is what you will be implementing.
- index.php (no need to modify) This is the starting point. You should point your browser to this file. It checks if the user is logged in. If not, it displays a login page.
- page xxx.php, form xxx.php, submit xxx.php (no need to modify) Once logged in, you will see several links in the menu bar. These links point you to page xxx.php files. These files may display input forms stored in the form xxx.php files. The forms are submitted to the submit xxx.php files.
- Files in the include directory (no need to modify) Helper files, php function definitions, and standard templates such as header.inc and footer.inc.
- Files in css, images, js (no need to modify) Files in css/ and images/ are standard presentation files. js/*.js files define javascript functions to provide a dynamic frontend interface. .js files are also used for AJAX.
- logout.php (no need to modify) This is the ending point where the user logs out and the session ends.
- Files in the scripts directory (no need to modify) Scripts to help startup the database, and run simple tests.
Of course, as we mentioned the exec XXX files return hard coded, meaningless data. As you put your own code in the exec XXX files, the real functionality will come into action.
10.3 Technology Background
In this section, we review the overall design of the system you will be implementing and the tools you will be using. As shown in Figure 1, the client (typically a web browser) sends requests to a server. On the server side, we will be using a three-tiered architecture consisting of a client browser, a web server (e.g., Apache with PHP extensions) and a DBMS. A client request arriving at the server invokes a PHP program that interacts with the database (typically to retrieve data) and generates an HTML page that is sent back to the client. The processing is done on the server side; the client does not even know that page is dynamically generated.
In this project we use XML as a way to provide an initial data set for all projects to use while allowing each project to design their own relational schema. We provide a routine to parse the XML into php objects which you may use to insert into the database using SQL. The output of Your Project will be php objects that the front end, and the grading scripts will use.
10.4 Array to XML
We also use the same data structure for converting to XML, which is implemented in the to xml() function in include/writexml.php file. You do not need to use this anywhere, but it would be good to know how it works. For example, if you have a PHP array tree as:
$foo = array(); $foo[0] = array(); $foo[0][’bar’] = ’chuck’; $foo[0][’baz’] = ’norris’; $foo[0][’animal’] = ’monkeys’; include_once(’include/writexml.php’); $xml_str = to_xml(’result’, ’thefoo’, $foo); echo $xml_str;
The above will print:
chuck norris monkeys
You can see that order of elements in the XML correlates with the order in which items are added in the array. In XML, the order of elements is significant - it should be the same as defined in the DTD or XML Schema. If you read documentation in the exec XXX files, we insist that you construct your return value array trees in the correct order as specified in the documentation and dummy code. This is because our grading scripts will convert the returned PHP array trees into XML and compare it with our results. Hence, the order in which you add items into result PHP array trees is very important.
10.5 Transactions
Transactions are used at the application/logic level to group SQL statements together. More specifically, when- ever a logcial modification of the database requires more than one SQL statement, a transaction wraps around the statements to ensure the logical operation either occurs or does not occur as a whole. Thus if statement 2 out of 5 statements fails, the database can rollback to a state before statement 1 was ever executed, leaving the database in a consistent state.
Chapter 16 in the book discusses transactions. SQL specific information can be found on pages 535-536.
Please read importXML.php for details and an example.
