Related questions
Concept explainers
Show transcribed data
Chapter 8 Homework Answer the questions below. In doing this homework, you don’t have to use a DBMS. If you didn’t create MSU Corporation datanase to do Chapter 7 homework, you can use the table information in “MSU Corporation Database” document attached. If you want to create the database now, use “MSUCorporationDatabase” script attached. Then, you may test your SQL statements with the database. Submission: Your answer document MUST show SQL statement for each question (No query results required). Copy and paste SQL statements Microsoft Word or PDF file and submit the Word or PDF file o Put question #, too. Due: Nov. 11 (Sunday) 11:59 PM I strongly recommend you start this homework earlier since it will take time to do. Before answer questions, have a clear understanding of table structures and relationships between tables There will be NO extension or make-up given. Note: When you join tables, you may use “old-style” join which is covered in Chapter 7. You may read page 323 if you don’t know what is “old-style” join. You can use any database software (MySQL, Oracle, SQL Server, etc.) to do this homework. I do not recommend any single-user DBMS (e.g., MS Access) because they are NOT 100% compliant to SQL standards. If you don’t have any DBMS, I recommend you use MySQL since you can download it free of charge. For download, installation, and tutorials information of MySQL, refer “Week 11&12 Materials and Resources” folder. Answer the following questions. Use the operator and/or statement asked. Otherwise, you will have zero (0) point for the question.
1. Change the Salary column in the Employee table to reject nulls.
2. Change the length of the CITY column in the Employee table to 50 characters.
3. Change the department name in the Department table to Administration for the department name entitled Admin and Records.
4. Add a department in the Department table. You choose the data for the new department.
5. Delete the new department you added in #4.
6. Add foreign key constraints to Assignment table.
7. Change the column name “Date_of_Birth” to “DOB” in Dependent table.
8. Create a new table structure named “dept_Locations” with “dept_no” and “location” fields. You also have to put primary and foreign key constraints so that this table is connected to “department” table.
9. Drop the new table you created in #8.
10.Create a new table named “employee_dependent” table with a SELECT statement. The new table will contain the following data: employee SSN, employee first and last name, employee address, dependent name, the relationship to employee. Note: You have to use SELECT statement.
11.Create an index named LNAMEINDEX based on the field (attribute) LNAME in employee table.
12.Create a view which contains the following information: employee SSN, employee first and last name, and project name assigned to each employee. You have to use 3 tables to create this view.
13.Create a database named “CBT”.
1. Change the Salary column in the Employee table to reject nulls.
Query:
ALTER TABLE Employee MODIFY COLUMN Salary INT NOT NULL;
2. Change the length of the CITY column in the Employee table to 50 characters.
Query:
ALTER TABLE Employee MODIFY COLUMN City VARCHAR2(50);
3. Change the department name in the Department table to Administration for the department name entitled Admin and Records.
Query:
UPDATE Department SET Name = 'Administration' WHERE Name = 'Admin and Records';
4. Add a department in the Department table. You choose the data for the new department.
Query:
ALTER TABLE Department ADD Department VARCHAR2(50);
5. Delete the new department you added in #4.
Query:
ALTER TABLE Department DROP COLUMN Department;
6. Add foreign key constraints to Assignment table.
Query:
ALTER TABLE Assignment ADD CONSTRAINT assignment_fk1 FOREIGN KEY EMP_SSN REFERENCES Employee(EMP_SSN);
ALTER TABLE Assignment ADD CONSTRAINT assignment_fk2 FOREIGN KEY Pro_Number REFERENCES Project(Pro_Number);
Step by stepSolved in 2 steps
- CUSTOMER PK CUS CODE CUS LNAME CUS FNAME CUS INITIAL CUS AREACODE CUS PHONE CUS BALANCE HE generates +x PK FK1 INVOICE INY NUMBER CUS CODE INV DATE VENDOR PK Y CODE V NAME V CONTACT VAREACODE V PHONE V STATE # CustomerCode CustomerName 10011 Leona Dunne 10014 Myron Orlando 10012 Kathy Smith 10015 Amy O'Brian 10018 Anne Farriss contains HD- Supplies PKFK1 PK NumOfProducts 6 6 3 2 1 FKZ -X LINE INV NUMBER LINE NUMBER FK1 P.CODE LINE UNITS LINE PRICE is found in # PRODUCT PK P CODE Use the above Customer Invoice ERD to write an SQL query that generates a list with the customer code, customer name, and the total number of Products they purchased. The report should be sorted by the number of products with the largest number at the top. Here is an example of how it should look like P DESCRIPT PINDATE P OOH P.MIN P.PRICE P DISCOUNT CODE Corper 200arrow_forwardHow can data dictionaries assist in formulating effective data retrieval strategies?arrow_forwardKindly explain the flow of data on the diagramarrow_forward
- Department deptnum deptname offers fax employs phone lo cation hod Student AcademicStaff studentid staffid firstname Course firstname E lastnam e E inancialloan coursecode la stname enrolls manages duration - qualifications address E coursetitle phoneextension deptn um offi cenumber sex dob staffid E address coursecode E position E sex E salary deptn um has consists of undertakes NextOfKin Раper firstname рарercode Assessm ent A lastnam e papertitle result E relationship E phone startdate score enddate papercode address resources studentid tstudentid coursecode For the above given database schema you are required to develop a SQL query for the creation of the Student, Department and the NextofKin tables also embedded all the constraint in them accordingly.arrow_forwardi want you to replace faculty with SEUarrow_forwardHow do DataReaders and DataSets differ?arrow_forward
- Only a and b and carrow_forwardSALESREP SalesRepNo 654 734 345 434 RepName Jones Smith Chen Johnson HireDate 01/02/2005 02/03/2007 01/25/2004 11/23/2004 CUSTOMER CustNo 9870 8590 7840 4870 CustName Winston Gonzales Harris Miles Balance 500 350 800 100 SalesRepNo 345 434 654 345arrow_forwardWhich process must be performed without accurate data?arrow_forward
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill Education
Starting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSON
Digital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON 
C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSON
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Programmable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education