SQL coding

Relational Database

1) You would like to bring all of this information together in a relational database system.  Consider what data elements or variables do you think are necessary? 

Table 1: Student ID, Names of students, Grades, Courses taken, Phone number.

Table 2: Job ID, Job role, Requirements, Location, Salary info.

2) How would you structure distinct tables?  (i.e., what would constitute a row in your table(s)?)

Students Table
Student ID	First Name	Last Name	Course	Grade	Phone
1	Jason	Griffin	Nursing	First Class	555-1290
2	Hayley	Fray	Data Scientist	Second Class	555-1204
3	Daniel	Morgan	Accounting	Second Class	555-1215

Job Listings Table
Job Listings ID	Student ID	Job Role	Type of Job	Location	Salary Info
1	3	ER Nurse	Full-time	New York	Paid
2	1	Accountant	Full-time	Chicago	Paid
3	2	Junior Data Scientist	Internship	Washington DC	Unpaid

3) How would you ensure information is stored efficiently? 

To store information efficiently we are going to split the job information into its table.

Student Table
Student ID	First Name	Last Name	Course	Grade	Phone
1	Jason	Griffin	Nursing	First Class	555-1290
2	Hayley	Fray	Data Scientist	Second Class	555-1204
3	Daniel	Morgan	Accounting	Second Class	555-1215

Job Listings Table
Job Listings ID	Student ID	Job ID	Salary Info
1	3	1	Paid
2	1	2	Paid
3	2	3	Unpaid

Job Table
Job ID	Job Role	Type of Job	Location
1	ER Nurse	Full-time	New York
2	Accountant	Full-time	Chicago
3	Junior Data Scientist	Internship	Washington DC

4) What keys would you use to relate tables to each other? (i.e., Describe how information in one table would link with information in another table)

The Student ID in the Students Table and the Student ID in the Job Listings Table are the keys that are used to relate the two tables.

The Job ID in the Job Table and the Job ID in the Job Listings Table are the keys that are used to relate the two tables.

5) If you wanted to add the capability of 'automatically' matching students to jobs and vice versa, explain via an illustrative example, how this automatic matching might work for your design.

By joining the Student ID key in both tables, the data from the two tables are automatically matched.

Data Table
Student ID	First Name	Last Name	Course	Grade	Phone
1	Jason	Griffin	Nursing	First Class	555-1290
2	Hayley	Fray	Data Scientist	Second Class	555-1204
3	Daniel	Morgan	Accounting	Second Class	555-1215

Data table Cont’d

Data Table
Job Listings ID	Course	Type of Job	Location	Salary Info
2	ER Nurse	Full-time	Chicago	Paid
3	Junior Data Scientist	Internship	Washington DC	Unpaid
1	Accountant	Full-time	New York	Paid