database homework

HW

1. Using the Order DB from last class – create an Access Database. See attachments “1- Starting to use Access.doc” and 6-Order-DB 3-15-2020.

2. Similarly, fill out the new Database – Enrollments (previous homework – in file Professor.xlsx

There is a sample attached for the book DB; But please submit your own work.

For simple help – e.g. meanings of icons on Access list: (Note we only use tables and queries, not -forms nor reports) http://www.gcflearnfree.org/access2013/4.1

or https://support.office.com/en-us/article/Database-design-basics-1eade2bf-e3a0-41b5-aee6-d2331f158280#__toc270678227

A sample of a 3rd database is shown – Database Book – to give ideas.

3. SQL Light (mandatory assignment): Writing an SQL query is becoming less of a chore; often we are given the query front end to ask questions of a database. That said, the SQL light query lesson from KHAN Academy will show you how to create simple SQL queries. I will not test you on SQL but you will be graded on showing proof that you finished the assignment. The lesson is easy and relatively short:

https://www.khanacademy.org/computing/computer-programming/sql/sql-basics/p/project-design-a-store-database

*****

If you want more SQL Experience, as some have requested, please see the other units at that site, e.g. Advanced SQL Queries. If you want I can look it over. This is for those who want more experience in SQL, but it will not be tested.

Please submit 4 files –

1. Order DB Access DB file

2. Enrollments Access DB file

3. the SQL statements copied from light HTML example for Khan Institute. Submit your queries and the output

4. the txt file with Multiple choice questions. This is a big help so I can you really understood.

Some hints for using Access

1. Do not choose values for the keys. Let the system choose them automatically (just tab to the next field).

2. Make sure the key fields have the exact name and are type number. E.g. student_no should be spelled the same in both the student table and the student-course table. In the student table it is a primry key – so let the system choose the value. as in 1 above. In the student-course table, it should be numeric type, but not a primary key. Use the value from the student table. But create a “dummy” primary key for the student-course table like this student_course_no. Just let the system fill it in, but never use it after that.

Same for professor_no and student_no. Same for all the other tables. ;This avoids a lot of difficulties later.

Thank You!