Go to the bin directory inside the root directory of your SQL server installation and type the following in command prompt:
C:\mySQL dir\bin> mysqld --console
You should see some messages from the server. One of the lines should read "ready to receive connections".
Alternatively, you could initially login as "root" and
create a user with sufficient privileges to create a database and create/drop tables in it.
The default configuration of the server is given by paramaters in the "my.ini" file.
To start the client, open up another command window and type the following: C:\some directory> mysql -u root -p You should be prompted for your password; this will be the password you chose during installation of SQL server. You should now see then mysql> prompt where you can now issue sql commands.
To check whether this database has been created, open another command window and try connecting as a client directly to this database.
C:\some other directory>mysql -u root -p -D moviedb
Upon providing your password, you'll be directly connected to the moviedb database that you created in the other client window.
You can verify that you haven't created anything in this database so far.
C:\some other directory>show tables; You should get back a message saying "Empty Set".
In an actual development effort, the functional and look-and-feel requirements for these functions would have been specified in every particular. We've purposely left these requirements high-level because we want you to use your skills, experience and reasoned judgment to refine the requirements. Do feel free to ask the instructor or TA for advice! With instructor or TA permission, you can also enhance these specifications for potential additional credit; for example, you can use the GUI rather than a console interface. But be careful: including additional features that do not work will detract from your score.
| Table
Name |
Attributes |
Notes |
| movies |
id:integer
(primary key) title:varchar(100) year:integer director:varchar(100) banner_url:varchar(200) trailer_url:varchar(200) |
required required required required URL of movie's "poster"; not required URL of trailer; not required |
| stars |
id:integer
(primary key) first_name:varchar(50) last_name:varchar(50) dob:date photo_url:varchar(200) |
required for a person with one name, put it in last_name & make first name NULL not required not required |
| stars_in_movies |
star_id:integer,
referencing
stars.id movie_id:integer, referencing movies.id |
all attributes required |
| genres |
id:integer
(primary key) name:varchar(32) |
all attributes required |
| genres_in_movies |
genre_id:integer,
referencing
genres.id movie_id:integer, referencing movies.id |
all attributes required |
| customers |
id:integer
(primary key) first_name:varchar(50) last_name:varchar(50) cc_id:varchar(20), referencing creditcards.id address:varchar(200) email:varchar(50) password:varchar(20) |
all
attributes required for a person with one name, put it in last_name & make first name 'non enforce uniqueness on both email and password. |
| sales |
id:integer (primary key) customer_id:integer, referencing customers.id movie_id:integer, referencing movies.id sale_date:date |
all attributes required |
| creditcards |
id:varchar(20),
(primary key) first_name:varchar(50) last_name:varchar(50) expiration:date |
all
attributes required for a person with one name, put it in last_name & make first name 'non |
Write a Java program that, using JDBC to interface with MySQL, provides for the following functionality. It can be console based (or GUI, as discussed above).
When this program is run, the user is asked for the the user name and the user password (the database user login info not the password in the above schema) . If all is well, the employee is granted access (and a message to that effect appears on the screen); if access is not allowed, it says why (e.g., the database is not present, the password is wrong). Allow a way for the employee to exit easily.
Provide a menu that allows the employee to do the following:
In cases where the requested tasks cannot be accomplished, print out a clear, crisp error message–do not just pass along some Java exception!
Upon completion of any command except the exit commands, the user is returned to the menu.
Each group will be assigned a unique group ID, which should be used to name your files and submission. In particular:
Those script files should be directly executable by being fed as input to the mysql command .
Login onto EEE using your UCInetID. Under our course COMPSCI 122B, click Dropbox. Under Folder View, click CS122B Project 1/Assignment Submission. Upload your zip file there. Please notice that after the deadline you will not be able to upload your files to the system any more.
This project was first drafted by Chen Li and Kirill Petrov in Winter 2005. It was modified by the following contributors: Norman Jacobson and Kirill Petrov (Spring 2005); Norman Jacobson, Chen Li, and Michael Sirivianos (Winter 2006); Chen Li and Michael Sirivianos (Spring 2006); Chen Li and Shengyue Ji (Winter 2007); Chen Li and Chaitanya Desai (Spring 2008); The data.sql file was built by combining student data sets from previous offerings. The aggregate data set was cleaned from duplicates and other errors using a PL/pgSQL script written by Eric Murakami and Chris Trezzo (Winter 2006).