Answer Happy

Problem 1. Create a database schema called University and in that create a table called student that is follows the following relational schema Student PK StulD:int Name:varchar(20) IName:varchar(20) credits:int major varchar(4) gradYear:date Where • stulD is the ID number of the student • fName is the first name of the student • IName is the last name of the student • credits is how many credit hours they have taken in their current degree so far • major is the short version of their major: CS for computer science, MATH for mathematics, etc • gradYear is the expect year and month (put not day) they will graduate Then add at least 5 records to this table using values that you generate (more is better though, but don't overdo it either). You may generate fake data (data not based on real people/places) but use realistic sounding data; example don't use asdfgg as the fName for instance. Submit three things: 1. The SQL code that you used to create the DB and table 2. The code that you used to insert the data a. Make sure that I can copy and paste your code into my copy of MySQL and run it. That means no screen shots or weird formatting in your code 3. A screenshot showing you displaying the data after it has been inserted
Problem 2. Take the previous DB schema that you created from problem 1 and add to it. Add tables to your University DB based on the following schema Professor Dept PK empID:int PK dept:varchar(4) fName varchar(20) fullName:varchar(30) office:varchar(10) IName:varchar(20) dept:varchar(4) empID is the ID of the professor. fName is the first name of the professor IName is the last name of the professor • Professor.dept is the department that the professor works in and is a foreign key which relates to the Dept table. This is a four letter shortened name of the department such as CS, MATH or IT Dept.dept is the shortened name of the department such as CS, MATH or IT o Please to note that this is NOT related to Student.Major Office is the location of the main office for that department After creating these two tables, populate professor with at least 3 records and Department with at least 2 records (more is better though, but don't overdo it either). You may generate fake data (data not based on real people/places) but use realistic sounding data; example don't use asdfgg as the fName for instance. After completing this, submit three things 1. The SQL code you used to create each table 2. The code that you used to insert the data a. Make sure that I can copy and paste your code into my copy of MySQL and run it. That means no screen shots or weird formatting in your code 3. A screenshot showing you displaying the data after it has been inserted You only need to submit materials related to the creation of population of the two new tables, you don't need to submit anything related to the Student table .
Problem 3. Create a new DB schema called Company and add tables to it based on the following relational schemas Employee Salary Department PK empID PK,FK1 employee D IName PK,FK2 deptID departmentName department salary office email paymentSchedule employeeCount office employementType You will need to determine the data types of each attribute yourself based off what you know about the real world. Be sure to choose a fitting datatype for each attribute. Provide and justification you believe is needed and be sure to state any assumptions to support your decision (you don't need to write a novel, one sentence per is fine). Be sure that the datatypes you choose will support the data provided below. After creating your DB and tables, populate (insert) Employee with the following data 1, 'Jack', 'ace', '[email protected]', 'ben 207' 2, 'Jack', 'hr', '[email protected]', 'hughes 108b' 3, 'Mr. Smith', 'it', '[email protected]', 'farnswoth 228' 4, 'uncle pennybags', 'inv', '[email protected]', 'main office 1' 5, 'Lizzie Magie', 'dev', '[email protected]', 'Arlington virgina'); Salary with 1, 'acc', 27.27, 'hourly', 'contract' 2, 'acc', 36.81, 'hourly', 'fulltime' 3, 'it', 11001.10, 'monthly', 'fulltime' 4, 'inv', 999999.99, 'continuous', 'fulltime' 5, 'dev', 500.00, 'once', 'contract'); PK deptID
Department 'acc', 'accounting', 'h6201 b', 2 'hr', 'Human Resources', '228 west', 1 'inv', 'Investment', 'Pawtucket RI', 1 'dev', 'Developer', 'Arden DE', 1 'it', 'Info Tech', '123 fake', 1