Question One
In this question you will create a database for the following description:
Items are produced by companies and ordered by customers. Customers can order many items and the same item can be ordered many times by many customers. Every individual item can only be produced by a single company; however, a single company may produce many items. All entities have unique IDs. Companies have names, descriptions and countries of origin. The country of origin should be stored as a 2 letter code. Customers have names, emails and phone numbers. Items have names, descriptions and prices. Orders should store information about what item was ordered by what customer and the date of the order.
A.On paper or using a drawing program of your choice, draw an ER diagram that captures these relationships. Ensure that you have clearly marked up your diagram with cardinalities, attributes and keys.
If a drawing program was used, export an image of your ER diagram and include it in the question1 folder for submission.
B.From your ER Diagram, generate a complete Relational Model (Relational schema); you can do this on paper or with a word processing program of your choice. If you use a digital file, include it into the question1 folder for submission. Apply normalization to your Relational Model, ensuring that compound attributes are broken up.
You should make it clear which attributes in your relational model are primary keys and foreign keys. It is suggested you do this with the following conventions:
Primary keys Underlined and bold
Foreign keys *
For example:
entityName(primaryKey, attribute, *foreignKey)
C.Finally, convert your Relational Model into MySQL compatible SQL, writing the DDL with appropriate data types in the provided ictgradschool/web/practice2/question1/question1.sql file. You should include all necessary DDL so that someone running the script can drop the tables and recreate the database. Create some sample data to insert into the tables, including it in the same file as your SQL DDL. Your sample data should include at least 3 rows for each table and allow you to test that all relationships between tables function as intended. Ensure that your SQL runs successfully on the MariaDB server.
Question Two
In this activity you will create a page that displays all of the information and images stored in the SQL database. It should look something like this:
While the files themselves are stored locally, the information about each of them will be stored in an SQL database. The SQLdatabase has been provided for you in the ictgradschool/web/practice2/question2/question2.sql file for this
Before starting this exercise:
●run the ‘question2.sql’ SQL script against your database to create the required table.
●ensure that you have setup your username, database name and password within the ‘connection.properties’ file.
Tasks:
●Complete the ImageInfo JavaBean so that it can store all of the information from the SQL table created by question2.sql
●Create a method (or methods) in the ImageDao class that will return a list of ImageInfo objects
●Complete the necessary code in the QuestionTwo servlet to get the list of ImageInfo objects, set them up as request attribute and then pass control to the JSP file
●In the JSP file, loop through the list of ImageInfo objects and display them on the page with the title, image and description