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...
-
The base of a solid is bounded by one arch of y = cos x, -/2 x /2, and the x-axis. Each cross section perpendicular to the x-axis is a square sitting on this base. Find the volume of the solid.
-
Determine whether f is even, odd, or neither. If you have a graphing calculator, use it to check your answer visually. x? |f(x) x* + 1
-
In 1951, DuPont began using the chemical perfluorooctanoic acid to manufacture Teflon. Due to the dangerous nature of the chemical, DuPont was given special instructions by its supplier to dispose of...
-
1. Prepare a context diagram for the new system. 2. Prepare a diagram 0 DFD for the new system. 3. Write a brief memo that explains at least five attributes that you can use to define a process in...
-
Data Input Box: Number of mortgages in initial pool 500 Average mortgage balance $300,000 Initial mortgage pool balance $150,000,000 Prepayment rate 7.50% Mortgage rate 4.50% Servicing and Guarantee...
-
Flowmaster Forge Inc. is a designer and manufacturer of industrial air-handling equipment that is a wholly owned subsidiary of Howden Industrial Inc. Howden is interested in selling Flowmaster to an...
-
1. Given the following: = {1, 2, 8, 9, 21, 25, 27) A = (1, 9) B = {1, 2, 9, 25, 27) C = {8, 9) Find: ANB AUC A - B
-
How does the concept of due diligence progress from a foundational understanding of knowledge to the detailed examination of data?
-
QUESTION ONE: consider which of the family law topics they most enjoyed studying, such as child custody and parenting time and a court appointed special advocates (CASA) in New Jersey. Please explain...
-
Determine which statement about U.S. Treasury bill & Gov't of Canada Treasury bill is false assuming positive quoted rate. I.A 90-day U.S. T-bill with maturity value 10,000 & price 9,800 has a...
-
Lloyd is a divorce attorney who practices law in Florida. He wants to join the American Divorce Lawyers Association (ADLA), a professional organization for divorce attorneys. The membership dues for...
-
Simon LLC produces smartphones, computers, and televisions. Although all high - level decisions are made at company headquarters by top management, rapid expansion and increasingly specialized...
-
Analyze the competitive landscape of the Canadian coffee industry, including the major players, their market share, and their strategies.
-
Marc Company assembles products from a group of interconnecting parts. The company produces some of the parts and buys some from outside vendors. The vendor for Part X has just increased its price by...
-
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...
-
The data in Table 8 on the previous page represent the marital status and gender of the residents of the United States aged 15 years old or older in 2013. (a) Compute the probability that a randomly...
-
For the fiscal year 2007, a tax authority audited 1.65% of individual tax returns with an income of \($100\),000 or more. Suppose this percentage stays the same for the current tax year. What is the...
-
The probability that a driver who is speeding gets pulled over is 0.8. The probability that a driver gets a ticket, given that he or she is pulled over, is 0.9. What is the probability that a...
Study smarter with the SolutionInn App