microsoft access

1a). Using the five tables below, create tables and link them through relationships. The tables should contain primary and foreign keys (where needed). (5 points)

Tables: City, Staff, Room, Hotel, and Guest.

1b). Using the five tables below, create tables and link them through relationships. The tables should contain primary and foreign keys (where needed). (5 points)

Tables: Book, University, Publisher, Library, and Author.

Section 2

2). Use the following information to create tables and link them through relationships. There are two pairs of relationships in this question. One of them is Department-Employee which has the relationship of one-to-many. The tables should contain primary and foreign keys (where needed). Do not create your own fields. Use the fields given to determine primary and foreign keys. Create three tables in Microsoft Access and input the data using forms (3 forms). Create a Form/Subform for the Department/Employee table and create a lookup column for BonusWitholdRate field in the Subsidiary table and Gender field in the Employee Table. Save the database as Subsidiary.

SubCode
SubName
BonusPayment
WithHoldRate
Revenue
Expense
S001
Lotus
875,000
.20
8,000,000
4,000,000
S002
Tulip
745,000
.18
6,000,000
2,000,000
S003
Daisy
925,000
.25
9,000,000
6,000,000
S004
Jasmine
765,000
.12
7,000,000
2,500,000
S005
Lavender
645,000
.10
7,000,000
3,500,000

DepartmentCode
Depart.Name
EmpNo
EmpName
EmpGender
D001
Finance
E001
Harry
Male
D002
HR
E002
Chris
Undefined
D003
Marketing
E003
George
Undefined
D004
Production
E004
Molly
Female
D005
R&D
E005
Angelina
Female

Create the following queries
1). Annual bonus after withhold which is more than $65,000
2). Annual net profit for each subsidiary.

Create a report based on the results of each query. Your first report should