A project proposal and a 15 page final project
Koree Matecki, Joveena Joy Raja DS 220 Proposal 4/8/2018 Motivation for solving the problem: Our goal is to extract information from a website’s source code and display it as an independent sentence or phrase through the use of regular expressions. Method using which the problem will be solved: We will be using R to write the code to solve our problem. By reading in a portion of HTML code, we will be able to extract snippets of data. We will provide the code used to conduct the extractions. Datasets that will be used to evaluate the solution (Remember to argue why your data is sufficient, i.e., representative of a broad sample of data over which the solution you proposed will be applied in real life and not an anecdote): https://developer.mozilla.org/en-US/docs/Learn/HTML/Forms/Your_first_HTML_form Above is the address of our dataset. It displays various HTML codes that we can use to extract information from. Metrics that will be used to evaluate the efficacy of the solution: We hope to pull data from the HTML codes to transform the information into a readable sentence. Our code will effectively display our goal. Experiments that you will run: We will experiment with smaller lines of code to show our process. Then we will show the same process with a tweet or Facebook post. Schedule listing milestones for your progress: Our first priority is to load out data into R. Then we can debug our program to accurately display our information. After writing adequate code, we can analyze it and begin to work with it. Original Proposal · For your proposal, we would like to see you consider and discuss the following: · What you would like your database to be about (what you want to store in your database) · A database at a movie store like a Blockbuster. The database consists of employee information, customer information and movie information. Within employee information we will have (EmployeeID (integer), EmployeeName (Char), Position(char), Phone Number(char), City(char), Salary(char)), within customer information we will have (CustomerID (integer), CustomerName(char), Phone Number(char), Email (char), Address (char), CheckedOutMovies (integer), OverdueMovies(Boolean)), within movie information we will have (MovieBarcode(Integer), MovieTitle(Char), MovieGenre(Char), MovieDirector(Char), RottenTomatoRating(Char), MPRARating(Char), InStock(Boolean), CustomerID(Char)). · How many tables you think this database might contain · The database will contain three tables. · How much data are you planning to load into this database · We will have ten employees, fifty customers, and hundred movies to choose from. · Why is using a database to handle this data useful? · A database would be useful in order to properly run our store. We would need to keep track of all the movies in stock and whether they are checked out or not and by who. If movies are late we need to know that as well in order to inform the customer. Employee information is necessary to keep track of as well. Theoretically someone could check this database before coming to the store to see if the movie they wanted was in stock, and what it was rated, and whether or not it was good as well. All of the information stored in the database would be useful for the customer to be able to do this. · How would people access this data once your project is complete? · There will be a simple user interface to access the data. · How do you plan to get access to all of your data? · We will use real movies, and then made up employee and customer information, and the data we will access through a simple user interface. · In addition to these main points, if you are in a group of two or more, we are also looking for you to explicitly state: · Who your team members are (if any) · Joveena · Gabby · Discuss how you intend to distribute the workload of this project within your group (if you are in a group) · We will split each table into half, 5 employee data, 25 customer data, and 50 movie data per person. We will paste it into a google doc and then at the end add all of the information into one SQL, but on our own to make sure the statements are correct will we respectively check them on our own PHP My Admin account. · Do you plan to meet regularly or as needed? · We will plan to meet regularly each Tuesday one hour before class and extra hours if needed. Modified Proposal: · After creating the database I made some changes to the original proposal. I encountered the need to add more tables to the database to manage all of the data. The first being rental which includes the rentalID as the primary key, and then movieID, employeeID and customerID as foreign key and then the checkOut date. This manages when the rentals are checked out, which customer checked them out, what they checked out, and who helped them check the rental out. The other table I added was a rentalDetails table which kept track of when the rental was due. For the employee table I decided to get rid of the additional information that I originally proposed and simply used an employeeID, employeeName, position, and phoneNumber. It did not seem necessary to include their city and salary for the purpose of this project. I also edited the customer table to solely include customerID, customerName, emailAddress, and phoneNumber. The movie table was edited to include the attributes movieID, movieTitle, movieYear, movieMRPARating, and a boolean inStock. Relational Diagram · In the diagram below we have five tables: Movie, Customer, Employee, Rental, and RentalDetails. In our store we only care one of each movie and for that reason a movie can either have 0 to 1 customer, meaning it can be in stock or out of stock. On the other hand to be a customer you do not need to currently have a movie checked out but you also have the ability to rent multiple movies giving it a one to many relationship. Each rental can only have one employee to have helped check out the movie giving it a one to one relationship, but an employee can have helped check out multiple rentals or it could have helped check out none because they may hold a position such as Night Guard giving it a 0 to many relationship. Each rental can only have one customer associated with it and must have a customer to be considered a rental but a customer can have 0 rentals or multiple rentals to be considered a customer and that would give it a 0 to many relationship. Each rental has 1 and only 1 rental details to detail it’s return date and each rental detail can only have one rental associated with it also giving it a one to one relationship. Create Table Statements: · This database was creating in the mysql program phpmyadmin and below are the following tables that were included. Our database includes five tables Movie, Employee, Customer, Rental, and RentalDetails. The idea behind these tables is to keep track of all of the movie information and employee and customer information. The rental table checks out the movie on said date and then gives that rental a rentalID connecting that rental to a specific customerID and the employeeID who checked that customer out. The rental details then connects that rentalID to when that rental should be due. Create Table Movie( movieID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, movieTitle CHAR(250) NOT NULL, movieYear INT(4) NOT NULL, movieGenre CHAR(50) NOT NULL, movieMPRARating char(10) NOT NULL, inStock boolean NOT NULL ); CREATE Table Employee( employeeID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, employeeName CHAR(75) NOT NULL, position CHAR(50) NOT NULL, phoneNumber CHAR(15) NOT NULL ); CREATE Table Customer( customerID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, customerName CHAR(75) NOT NULL, phoneNumber CHAR(15) NOT NULL, emailAddress CHAR(100) NOT NULL ); CREATE Table Rental( RentalID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, MovieID INT NOT NULL, CustomerID INT NOT NULL, EmployeeID INT NOT NULL, CheckOutDate Date NOT NULL, CONSTRAINT EmployeeID_FK FOREIGN KEY(EmployeeID) REFERENCES Employee(EmployeeID), CONSTRAINT CustomerID_FK FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID), CONSTRAINT MovieID_FK FOREIGN KEY(MovieID) REFERENCES Movie(MovieID) ); CREATE Table RentalDetails( RentalID INT NOT NULL, ReturnDate Date NOT NULL, CONSTRAINT OrderMovieID_PK PRIMARY KEY(RentalID), CONSTRAINT RentalID_FK FOREIGN KEY(RentalID) REFERENCES Rental(RentalID) ); Queries and Movifying Statements SELECT movieTitle FROM movie WHERE movieGenre like "Romance%"; · This query shows all of the movies that fall in the movie genre of Romance, which is useful when wanting to displace that certain genre. Select movieYear From movie Where movieTitle = 'Logan' · This query shows the year in which the movie Logan was released, if the database where to go in more depth it could be helpful so one could create a page showing new releases. Select CustomerName From Customer Where CustomerID IN( Select CustomerID From Rental Where MovieID In( Select MovieID From Movie Where MovieTitle = 'Inside Out') ); · This query displays the customer name for the customer who checked out the movie Inside out. SELECT C.CustomerName,C.EmailAddress FROM Customer AS C LEFT JOIN Rental AS R ON C.CustomerID = R.CustomerID WHERE MovieID IS NULL; · This query displays all of the customers that currently do not have any rentals checkout. Update Employee Set`position` = 'Night Guard' Where `employeeID` = 6; · This query updates the position of employeeID 6 to Night Guard which could be important if your employee gets a promotion