small design implementation

Website: http://famouspoetsandpoems.com/poets.html

Based on the link received, post the following information, in the respective questions.

  1. A PDF document with all the description of the homework as required below
  2. A Workbench design file with the diagram describing your model, as specified below
  3. The MySQL script creating a database and making the required queries, that will implement the design completely.

Start all your file names with your email id (e.g. mboicu for me), followed by (-s3a1-), and you may add a name for your database.

DATABASE SCOPE:

You must create a entity design involving FOUR entities with a 1:1/1:M/M:M relationships for the model provided.

DESCRIPTION:

Your homework must include the following sections.

SECTION 1 WEB SITE DESCRIPTION (0.05 points)

You must include one or more screen shots showing the required model data. At least 5 data items (entity instances, rows) must be included for each of the entities.

SECTION 2 DATABASE REQUIREMENTS (0.15 points)

You must include two phrases, each one phrase describing one of the entities as in requirements. You must identify at least 3 descriptive attributes (columns) i.e. (You must clearly describe the 3 attributes in each of the entities as Composite/simple attributes: why is composite, or why is simple, multi-valued/single-valued attributes: why is multi-values or why is single-valued, unique/not unique attributes: why is unique, why are not unique, required/optional attributes: why are required; why are optional), in addition to the primary key (id), and foreign key, if any. If your link does not contain 3 attributes, you must identify the ones present.

You must include a phrase describing the relationship types (1:1/1:M/M:M) between the entities. For instance if there exists a 1:1 relationship between any two entities in your design then you must follow a similar pattern with: One faculty may/must have a faculty description. A faculty description belongs to one faculty.

For instance, for the class example IST Faculty, one may include:

A list with all faculty is stored and managed. For a faculty we keep the G number (id), name, photo, academic title (e.g. Associate Professor) and administrative title (e.g. Chair), if any.

SECTION 3 DATABASE ERD MODEL (1.5 points)

You must create the ERD complete design of the four entities and the relationship between them.

Include an image with the design in the PDF file. Be sure, you create the corresponding attributes and specify if they are required/optional and unique or not. Be sure, your relationship has a name, correct type, strength, entity participation, and the FK is included in the child entity.

Also, be sure you include the source design file as an attachment to the corresponding question. No points given without the source file, or a different source file.

SECTION 4 CREATE DATABASE SCRIPT (0.5 points)

You must implement the design as presented. To do so you must create a SCRIPT file that performs the following operations:

  • Drop the database if already exists. Create a database with a suggestive name. Mark that database as default. (0.1 points)
  • Create the tables with their relationship (FK constraints must be provided) (0.1 points)
  • Show the tables in the database and show the structure of the created tables (0.1 points)
  • Insert at least 7 rows in each of the tables, if possible (0.1 points)
  • Show the content of the tables (0.1 points)

In the PDF file, you must include your script and an execution of the script either in command line or in MySQL Workbench (screenshots). The source file/screenshots must be signed with your name during execution (e.g. including comments). Image or file is not signed (-0.30 points)

You must attach a runnable SQL SCRIPT (a text file), containing the code for both section 4 and section 5. If not included, or not runnable, no points will be given.

SECTION 5 SIMPLE QUERIES SCRIPT (0.8 points = 8*0.1 points)

Continue the script file from the previous section to perform the following eight types of queries.

For each query, you must include in the PDF file the following aspects:

  • A description in English of what the query is intended to do (this is what the query will be check against)
  • The source of the query
  • The resulted table after the execution of the query

It is very important that the query will make sense in the context of the database, i.e. one can imagine a real life scenario when such a query will be made. A query that makes no sense or does not follow the intended description will receive 0 points.

Construct eight queries, based on at least two tables and satisfying the following requirements:

  • Query 1: Contains at least a calculated column and selects only some of the rows based on a condition (at least 2 tables)
  • Query 2: Contains only some of the columns in the tables and contains at least one logical operator and a function, the results are ordered based on a simple criteria (at least 2 tables)
  • Query 3: Contains only some of the columns in the tables and contains at least two logical operator and two or more functions, the results are ordered based on a two column criteria (at least 2 tables)
  • Query 4: Using at least one OUTER JOIN construct a query to join at least 2 tables. The query must contain some of the columns (from the tables joined), at least one logical operator and a function, the results are ordered in descending order.
  • Query 5: Using at least one OUTER JOIN construct a query to join at least 3 tables. The query must contain some of the columns (from the tables joined), at least one arithmetic operator and a function, the results are ordered based on a simple criteria.
  • Query 6: Contains only some of the columns in the tables and contains at least two arithmetic operator and two or more functions, the results are ordered based on a two column criteria (at least 3 tables)
  • Query 7: Contains all the columns in the tables and contains at least one arithmetic operator and a function, the results are ordered in descending order (at least 3 tables)
  • Query 8: Contains only some of the columns and a calculated column in the tables and contains at least one logical operator and a function, the results are ordered based on a complex criteria, with at least 3 columns involved, some columns ascending, some descending. (at least 3 tables)

Note: SQL SCRIPT (a text file) should NOT be generated by MySQL Workbench. If it is generated by MySQL Workbench (No points will be given for the script file)

Attached file: Sample of PDF format.