I am having a hard time with his project. If you could please type out an easier

Question

I am having a hard time with his project. If you could please type out an easier way to make queries and do the whole entire work sheet. Thank you. Please have steps on the questions as well if possible

Database Project Database Administrator Position . Starting date You arrive at Secure Systems, Inc.. for a database administrator position interview. After meeting the Human Resources coordinator, you are given a test to demonstrate your skills in Access. You are asked to create a database from scratch to keep track of all the candidates for the positions currently open at Secure Systems. (We assume that a given candidate can be interviewed for one job opening/position only, and for a given job opening/position many candidates can be interviewed.) Use these r a. Create and Name the database DP_ITE120 YourastnameFirstname. (5points) (Lee John) Create two tables: Candidates, and IobQnenings. Create the fields for each table using fbelow.(70 points for each table: 140 points total) b. the suggested fields below. Populate the tables with records indicated in e and c. Include a field to rank each candidate on a scale of 1 to 5 (5 is highest) d. Set the table relationship between Candidates and IobQnenings. Enforce the referential integrity. (45 points) Add 10 candidates - yourself and 9 other students in your class in the Candidates table. (Can be any name) Add the Database Administrator job and four (4) other sample jobs in the e. f. Create a query that lists all the database Administrator interviews with a ranking of 4 or 5. Add the last name and first name of the candidate to the query design. Run and save the query. Choose a name for the query. (10 points) g. h. Close the database. Submit it in Blackboard in the Database Project dropbex link in the Database Project folder under Projects/Study Guides tab Candidates tab Candidate ID (to be used as primary key) First Name Middle Name . Last Name Address .Zip code State Gender Rankin Highest degree Field of study Job Number (to be used as foreign key) . tabl Job Number (to be sued as primary key) . Title escription . Yearly Wage

Explanation / Answer

CREATE DATABASE DP_ITE120_yourlastName;

Create Table JobOpenings(JobNumber int PRIMARY KEY,

Title varchar(20),
Description varchar(100),
YearlyWage int);
CREATE Table Candidates(CandidateID int PRIMARY KEY,
FirstName varchar(30),
MiddleName varchar(30),
LastName varchar(30),
Address varchar(70),
ZipCode varchar(6),
State varchar(15),
Ranking int,
Gender char(1),
HighestDegree varchar(20),
FieldOfStudy varchar(10),
JobNumber int FOREIGN KEY REFERENCES JobOpenings(JobNumber)
);   

d. refrential integrity enforced while creating tables only.
e. INSERT INTO JobOpenings values ( 1,'Admin' ,'testing', 9999);

INSERT INTO JobOpenings values ( 2,'Admin 2' ,'testing', 1010);

INSERT INTO JobOpenings values ( 3,'Admin 3' ,'testing', 9081);

INSERT INTO JobOpenings values ( 4,'Admin 4' ,'testing', 1211);

INSERT INTO JobOpenings values ( 5,'Admin 5' ,'testing', 5555);

6.
INSERT INTO Candidates VALUES(101,'test FN','test MN','test LN','abc city area', '301201' , 'F' , 4 , 'sec school' , 'CSE', 1);

//likewise insert 10 records keeping in mind jobNumber can be only those values that are present in jobopening table.
7.
SELECT c.firstName,c.LastName,j.title FROM Candidate c,JobOpenings j WHERE c.JobNumber = j.JobNumber and c.rank > =4;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.