Assignment Background
In this assignment, you will develop a web application that will allow players to receive
cards from a collectible card game and trade those cards amongst their friends. The
card data used for this assignment is taken from the game Hearthstone. This is the
same data used in tutorial #8 a description of the structure of the data is provided at
the end of this document. A database-initializer.js file has been provided that will
create an empty ‘a5’ database in MongoDB and add each card to a ‘cards’ collection.
Note that the initializer will also delete any information in the ‘a5’ database. Each
user that registers for your web application will maintain their own set of cards and their
own set of friends. Friends within the application will be able to view each other’s cards
and propose/accept trades with each other. All data your server uses for this
assignment (cards, user profiles, session data, etc.) must be persistent. Your server
should be able to be restarted at any point and all the data should still be present.

In this assignment, you will develop a web application that will allow players to receive
cards from a collectible card game and trade those cards amongst their friends. The
card data used for this assignment is taken from the game Hearthstone. This is the
same data used in tutorial #8 a description of the structure of the data is provided at
the end of this document. A database-initializer.js file has been provided that will
create an empty ‘a5’ database in MongoDB and add each card to a ‘cards’ collection.
Note that the initializer will also delete any information in the ‘a5’ database. Each
user that registers for your web application will maintain their own set of cards and their
own set of friends. Friends within the application will be able to view each other’s cards
and propose/accept trades with each other. All data your server uses for this
assignment (cards, user profiles, session data, etc.) must be persistent. Your server
should be able to be restarted at any point and all the data should still be present.

1. (13 points) Clean and normalize the data. Submit your processed Excel file with a sheet for each relation/table. You do not need to submit screenshots of the Excel data.
a. The raw data contains a multivalued field (Country) that must be resolved before loading the data into APEX
b. The raw data contains a composite field (Laureate) that must be resolved before loading the data into APEX. Hint: Every laureate has a first name. Title, middle name, and last name are optional.
c. Break up the raw data into 3 tables. LAUREATE records information about each woman who has won a Nobel Prize, including ID, name (title, first, middle, last), birthdate, and death date. COUNTRY tracks the ID and country for each laureate. AWARD contains information about the award itself (year, recipient ID, category, reasoning, and whether the prize is shared). Assume that the same person cannot receive multiple prizes in the same year.
2. (23.5 points) Import the data into APEX.
a. Write a CREATE TABLE SQL statement for each table. Select appropriate data
types, sizes, and constraints for each field. Dont forget to add primary and foreign key constraints. Provide screenshots of your SQL statements in your Word/PDF file. Note: Do not use the Table Design wizard or Data Workshop to generate the CREATE TABLE code.
b. Use the Data Workshop to move your data into APEX. You can use the copy and paste or file upload method. Provide screenshots of the first few rows of your populated tables from the Object Browser.
3. (13.5 points) Use the database youve created to answer the following questions with simple queries (each query uses data from only one table). Provide screenshots of your SQL code and results tables in your Word/PDF file.
a. Which countries have had the most female winners? For the top six countries with the most female laureates, return the country name and total winners. Make sure to rename any new, calculated fields.
b. Many Nobel Prize winners have advanced degrees and titles like Doctor or Professor, but which winners do not have any titles? Return their IDs, full names (formatted as one field), and birthdates. Make sure to rename any new, calculated fields.
c. Some Nobel Prizes are awarded to individuals, and some may be shared among multiple people. For all instances where a woman won the prize individually in a category other than Literature or Peace, return the year, category, and reasoning for the award.

A small university is in the process of creating its first database system, and you are the chief architect of this system. This university does not have majors, faculties, and departments, but it does have programs. A program usually has 5 courses (in real situation, a program usually has much more courses, but to make the test data preparation easier, we set this number as 5), For simplicity, all the courses in a program are required and fixed. Prerequisite is not considered.

This university runs in a two-semester system: every year has Winter semester and Fall semester. In every semester, a course may be offered once or not be offered. A course-offering entity is necessary. A professor may teach any courses, and will teach one or more courses each semester. A student may register in a few programs. A course is passed when the grade is 60 or more.

Every person in the campus, a student or a professor, has an ID. This ID is uniformly managed, that means all the IDs are from the same pool, there is no special digit can be used to distinguish professors or students.

(6 marks) Using MySQL Workbench to create an ER model of this new database.

(4 marks) Convert the model into SQL statements which create tables. All the tables must be normalized or denormalized

(5 marks) Create a view Transcript, which is the transcript of all students. It should contain the following columns: studentID, studentName, course, year, semester, grade, professorName. Insert a few data items into the tables. Prepare a select statement to show a students transcript.

(5 marks) Create a view: TeachingProfile, which shows the teaching history of all professors. It should contain the following columns: ProfessorID, ProfessorName, course, year, semester, enrollment. Insert a few data items into the tables. Prepare a select statement to show a professors teaching profile.

(5 marks) Create a view Audit, which is the audit of all students. It should contain the following columns: studentID, programID, neededCourse, (such a row indicates that this course has not been taken or has not passed.). Insert a few data items into the tables. Prepare a select statement to show a students audit.

The design of the tables/relationships should be intuitive, straightforward, and should support university activities. Item 3, 4, 5 are the university activities.

Draw an ER diagram using the IDEF1X Notation and ERWIN for the following two applications. If you discover that the following narrative is incomplete, make up reasonable explanations to complete the description. State clearly your assumptions. Your assumptions should not contradict the assumptions stated in the narrative. You may download a trail version of ERWIN at http://erwin.com/

1.The software maintains and manages information related to  employees, rooms, committees, and committee meetings of  a company. The information
include at least the following data:

Employee: IDNumber, Full Name, Office Location, Telephone Number, Department,  and Position.

Employees Schedule: Date, Start Time, End Time, and Task .

Room: Building Number, Room Number, and Capacity.

Room Schedule: Date, Start Time, End Time, and Status.

Committee: Title, Chairs Name , ChairsIDNumber, and  Members.

Committee Meeting: Meeting Number, Date, Start Time, and End Time.

2. A software product that records and tracks all your bank accounts. The main menu shows the following possible transactions:

NewAccount: Enter information of a new bank account: BankName, Account#;

DeleteAccount: Delete an account;

Deposit: Enter information of every deposit transaction using Account# or BankName: Date, Amount, amount of CashOut;

Withdraw: Enter the information of every withdraw transaction using Account# or BankName: Date , Amount;

EnterCheck: Enter information of each check you write: Check#, CheckDate, Amount, ToWhom, ForWhat;

EnterStatement: Enter the information of each bank statement you receive: StatementDate, StartDate, EndDate, Balance, and a single line for each,

  Check cashed including: Check#, CheckDate, Amount, DateCashed;
  Deposit: Date, Amount;
  Withdraw: Date, Amount.

The project charter is already done, just uploaded it so you know how it started off as. The presentation slides from slide 37 to 40 are the only relevant ones ignore the rest. In slide 39 all you have to write about is “Detailed Communication Strategy” and “Consideration of the impact of GDPR” You don’t need to do the rest as my Team members are working on those parts 

Create a table with two columns. Name the table First Initial _ Last Name (e.g. John Dow
will create table j_dow). You have to audit all DML statements on your table. To do this you write two triggers:
1. To log any DML statements that users might run on this table. The results must be stored in the First Initial _ Last Name _ Log table (e.g. John Dow will create table j_dow_log).
The table should have unique event ID, values for both the Oracle and the system user who ran the query, the time it was executed and the type of DML query user ran.
2. To capture any data that was changed in the table. The results must be stored in the First Initial _ Last Name _ History table (e.g. John Dow will create table j_dow_history).
The table should reference the event ID from the log table, and store both old and new values for both columns.
What to submit: Name this file according to submission guidelines and include the following answers:
Code to create all three tables, constraints, insert statements. (10 points)

Code for the first trigger (10 points)

Test cases and expected results for the first trigger (30 points)

Code for the second trigger (10 points)

Test cases and expected results for the first trigger (30 points)

Step 1. Understand the specification of the problem
Read the following paragraph carefully. Multiple times, if necessary. Then follow the process we learned in Lab 2 to annotate the primary terms and actions.

Basketball is one of the most popular sports in the United States.  Basketball tournaments consist of games between two teams at various stages (league, playoff, final, etc.). Each individual game in the tournament is played between two competing teams played at a specific city or stadium with a certain capacity, and attended by a number of spectators. The matches may also be televised on a TV channel, and viewed by an estimated number of TV viewers. During each match, the teams typically play two halves (which may be further divided in quarters depending on the tournament). Players score points, commit fouls and perform blocks, rebounds, and assists. Each match ends with a score with the team with the higher score being the winner. Players play for a team and have a position and a jersey number in addition to physical attributes like weight, height, birthday, etc. Players may switch teams from one season to the next. In our scenario, let’s assume that they do not switch teams mid-season. Each team has a support team with at least a coach, assistant coach, and physio.

Step 2. Identify Terms, actions and Business Rules
Start an empty Microsoft Word document, and put your name and PantherID at the top of the document. Now for Step 2 – first write down all the terms that you identify that you feel should be entities. Then write down all the actions between the terms. Then write down as many business rules that you can think of.

Step 3. Draw an ER model for the scenario.
Create an E-ER model for the above scenario using the diagramming tool of your choosing, but follow the constructs we used in the lab. Make sure you indicate all your strong entites, weak entities, supertypes and subtypes, cardinality and participation indicators, and primary keys for every strong entity. Make sure you come up with at least 3 attributes for each entity.

Copy and paste this diagram into your Word document.

Step 4. Relational Schema
Design the schema of the database from your E-R diagram. You can do this by just listing all the tables and attributes in the Word document. Indicate the primary keys with underlines and foreign keys with asterisks as we did in the lab.

Finally, write an SQL script with the CREATE TABLE,  ALTER TABLE commands to create the complete schema of your design. Then create at least one row in each of the tables using insert statements. Your script should include the DROP, CREATE, ALTER and INSERT statements for creating all the tables and keys, followed by inserting at least one row in each table. You may need to insert more than one row in some tables. Save this script SEPARATELY in a text file (extension .txt). Do not include the DDL statements in your Word Document.

What to Submit
You will submit the following four documents:

The Word document containing your design, including the business rules, and all notes leading to your design. (.doc or .docx)
The original ER model in PDF format (.pdf)
The SQL script containing your schema creation and insertion commands (must not be in the Word document, and submitted as a .txt file)
Test your commands in livesql and make sure all commands run properly. Submit the transcript of running your script in LiveSQL in PDF form (in PDF – must show all tables created and rows inserted). (.pdf)

2. Design a database that will be used to store information for an IT Consulting Company that provides services for client organizations as well as purchases and resells equipment (servers, desktops, software, routers, etc.) to clients. Ensure that you diagram the
tables, columns / datatypes, and table relationships.
3. Create the database and tables from the previous step as well as populate the tables with at least 50 records (use a bulk import process). Also that the tables have the appropriate indexes and database options.
4. Create a SQL login and user that can be used to access and modify records within the database, as well as a separate SQL login that can only read records within the database. Use appropriate names for each one.
5. Ensure that you can isolate performance problems when they occur, as well as monitor ongoing SQL performance and usage. Optimize the performance of your database by performing the appropriate tasks. Ensure that your database is regularly checked for
inconsistencies and backed up. Any problems should be emailed to your trioscollege.com email address.
6. Create a new database that will be used as a data warehouse. Identify which information from your database is most valuable for business intelligence analysis. Next, define your dimensions for the data warehouse as well as the data points to store in the fact
table. Finally, select the columns to include in each dimension table, and optionally implement your design by creating the necessary SSIS packages.
7. Detach your databases and submit them to your instructor for marking.