Aptech Computer Educationis a training institute located in Karachi, Pakistan. The institute provides training in various...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Aptech Computer Educationis a training institute located in Karachi, Pakistan. The institute provides training in various courses in field of Computer Science such as, CPISM,DSIM and so on. Recently, the institute started using a primitive database system in which it stores the student and staff details. In the current database, there is only one table to store the student details and another to store the staff details. However, the users are finding it very difficult to trace student and staff records since, all the details are in the same table. (Assumption: Database is already created.) You as a database developer have to accomplish the following tasks: 1. Create separate tables to store student details, marks details, employee details, salary details, and course details. 2. Create required columns in each table to store the relevant details. 3. Use appropriate data types for the columns to store the different type of data such as name, phone, email, salary, fees, and so on as per requirement. 4. Apply appropriate check constraints on the type of data to be stored in the respective columns. 5. Set the primary and foreign key constraints on appropriate columns to relate the relevant tables. 6. Insert new records in each table and verify that all the check constraints are working properly. 7. Update and delete records in the table to ensure primary and foreign key constraints are working properly. 8. Identify columns that may hold null value and apply constraints accordingly. A Study Help Dashboard - Solution x A Lab 07 G Lab 07.pdf - Google Drive A classroom.google.com/c/NjlxNTY2NzgxMTVa/m/NZQ3NDY5OTA2MDBA/details SP2020 DB Lab EM/CM LAB ASSIGNMENT Question No 01 1. Create the following tables and show their structures: Employees(employeelD, firstName, lastName, birthCity, hiringDate, deptID, joblD), Departments(deptID, deptName, locationID), Jobs(iobID, jobTitle, salary), Locations(locationID, city, country). Job IDs should have the format "ENG123" for an engineer, "MAN456" for a manager etc. 2. Ensure that all numerical IDs increment automatically. Show the relevant table structures. 3. Set the location field to hold the value "Islamabad, Pakistan" unless otherwise specified. Show the relevant table structures. 4. Insert least 6 relevant records in each table. Display all the data. 5. Ensure that employee names and department names are never empty. Show the relevant table structures. 6. Delete any location and ensure that all departments at that location are also deleted. Show all relevant queries, table structures, and data. Question No 02 Activate Windows Go to Settings to activate Windows. Aptech Computer Educationis a training institute located in Karachi, Pakistan. The institute provides training in various courses in field of Computer Science such as, CPISM,DSIM and so on. Recently, the institute started using a primitive database system in which it stores the student and staff details. In the current database, there is only one table to store the student details and another to store the staff details. 4:46 PM O Type here to search A O d) 4/10/2020 Aptech Computer Educationis a training institute located in Karachi, Pakistan. The institute provides training in various courses in field of Computer Science such as, CPISM,DSIM and so on. Recently, the institute started using a primitive database system in which it stores the student and staff details. In the current database, there is only one table to store the student details and another to store the staff details. However, the users are finding it very difficult to trace student and staff records since, all the details are in the same table. (Assumption: Database is already created.) You as a database developer have to accomplish the following tasks: 1. Create separate tables to store student details, marks details, employee details, salary details, and course details. 2. Create required columns in each table to store the relevant details. 3. Use appropriate data types for the columns to store the different type of data such as name, phone, email, salary, fees, and so on as per requirement. 4. Apply appropriate check constraints on the type of data to be stored in the respective columns. 5. Set the primary and foreign key constraints on appropriate columns to relate the relevant tables. 6. Insert new records in each table and verify that all the check constraints are working properly. 7. Update and delete records in the table to ensure primary and foreign key constraints are working properly. 8. Identify columns that may hold null value and apply constraints accordingly. A Study Help Dashboard - Solution x A Lab 07 G Lab 07.pdf - Google Drive A classroom.google.com/c/NjlxNTY2NzgxMTVa/m/NZQ3NDY5OTA2MDBA/details SP2020 DB Lab EM/CM LAB ASSIGNMENT Question No 01 1. Create the following tables and show their structures: Employees(employeelD, firstName, lastName, birthCity, hiringDate, deptID, joblD), Departments(deptID, deptName, locationID), Jobs(iobID, jobTitle, salary), Locations(locationID, city, country). Job IDs should have the format "ENG123" for an engineer, "MAN456" for a manager etc. 2. Ensure that all numerical IDs increment automatically. Show the relevant table structures. 3. Set the location field to hold the value "Islamabad, Pakistan" unless otherwise specified. Show the relevant table structures. 4. Insert least 6 relevant records in each table. Display all the data. 5. Ensure that employee names and department names are never empty. Show the relevant table structures. 6. Delete any location and ensure that all departments at that location are also deleted. Show all relevant queries, table structures, and data. Question No 02 Activate Windows Go to Settings to activate Windows. Aptech Computer Educationis a training institute located in Karachi, Pakistan. The institute provides training in various courses in field of Computer Science such as, CPISM,DSIM and so on. Recently, the institute started using a primitive database system in which it stores the student and staff details. In the current database, there is only one table to store the student details and another to store the staff details. 4:46 PM O Type here to search A O d) 4/10/2020
Expert Answer:
Answer rating: 100% (QA)
CREATE TABLES SQLCREATE TABLE EMP EMPID NUMBER 4 FIRSTNAME VARCHAR2 10 LASTNAME VARCHAR2 10 BIRTHCITY VARCHAR2 10HIRING DATE DATEDDMMYYYYDEPTID NUMBER ... View the full answer
Related Book For
Business Statistics a decision making approach
ISBN: 978-0133021844
9th edition
Authors: David F. Groebner, Patrick W. Shannon, Phillip C. Fry
Posted Date:
Students also viewed these databases questions
-
Is it accurate to say that there is only one correct answer to every tax research question? Discuss the reasoning for your response. In what ways does your answer affect how the tax research process...
-
Using Theorem 11.9.2, show that there is only one linearly independent price vector for the closed economic system with exchange matrix 550
-
In the lemons model, there is only one price even though the products differ in quality. Why is that? What factors deter-mine that price? How does the price affect the quantities traded of the...
-
A paper recycling company converts newspaper, mixed paper, white office paper, and cardboard into pulp for newsprint, packaging paper, and print stock quality paper. The following table summarizes...
-
What is special about the game of Chicken? How does the game of Chicken differ from the prisoners' dilemma game?
-
R = log where log (A) A - the measure of the amplitude of the earthquake wave Ao the amplitude of the smallest detectable wave (or standard wave). An earthquake is measured with a wave amplitude 626...
-
Capstone Consolidated, Inc. is a leading manufacturer of steel products. The following inventory data relates to the firms production during the first quarter of 2015: At the end of the first quarter...
-
On January 1, 2011, Antonio Company issued $700,000 in bonds that mature in 10 years. The bonds have a stated interest rate of 8 percent and pay interest on June 30 and December 31 each year. When...
-
What are the three main investment risks that individuals face when working towards a short - term goal?
-
On March 1, 2008, Ginny Tyler established Seltzer Realty. Ginny completed the following transactions during the month of March: a. Opened a business bank account with a deposit of $30,000 from...
-
A random sample of n=36 observations is drawn from a population with a mean equal to 20 and a standard deviation equal to 18. Complete parts a through g below
-
(6.1) 10110 XOR 11011 Perform the following BITWISE logic operations. (6.2) 1110110 NAND 101101
-
Differentiate the function. y = eax4 y'(x) =
-
Balance, September 1 Checks BANK STATEMENT Deposits Other Balance $ 1,940 September 7 NSF check $ 90 1,850 September 11 $ 2,910 4,760 September 12 #101 $ 770 3,990 September 17 #102 1,640 2,350...
-
How do demographic shifts, such as an aging population and changes in workforce dynamics, impact the sustainability of the Social Security system? Consider the challenges posed by a larger retiree...
-
Going back to this week's recording, revise what you learnt about the doctrine of precedent. In the famous House of Lords case of Donoghue v Stevenson [1932] AC 562, the Court determined that the...
-
You've observed the following returns on bennington corporation's stock over the past five years: 1 5 percent, - 1 5 percent, 1 7 percent, 2 7 percent, and 1 0 percent a . what is the arithmetic...
-
Outline some of the major problems confronting an international advertiser.
-
Data were collected on a quantitative measure with subgroups of four observations. Twenty-five subgroups were collected, with the following results: a. Determine the Shewhart factors that will be...
-
MPC makes personal computers that are then sold directly over the phone and over the Internet. One of the most critical factors in the success of PC makers is how fast they can turn their inventory...
-
You are given two paired samples with the following information: Based on these paired samples, test at the = 0.05 level whether the true median paired difference is Item Sample 1 Sample 2 234 245...
-
When reviewing an organizational chart, what represents the formal line of authority and responsibility within the organization? A. Specialization of labor. B. Chain of command. C. Span of control....
-
Which type of structure has decision making and power being led by a few people? A. Flat. B. Integrated. C. Matrix. D. Tall.
-
Organizational culture and climate are important aspects of the organization. Which of the following are characteristic of the culture of the organization? A. Supervisor support is given. B....
Study smarter with the SolutionInn App