**Part****Designing a Database for an Access Control System**The aim is to manage who has access to which doors at specific times.**Entities in the Database:**- **Users**- **Groups** - A user can be assigned to more than one group.- **Doors**- **AccessRules** - Determines which group can access which door at which time schedule. - Example: Group A can access the Front Door on Monday from 7am to 9pm. - Example: Group B can access the Back Door on Saturday from 8am to 10pm.- **TimeSchedules** - Simplified to support only weekly schedules. - Columns in the TimeSchedules table: - Id - DayOfWeek - StartTime - EndTime - Example: A schedule with DayOfWeek=Monday, StartTime=7:00, EndTime=23:00 means access is granted on Monday from 7am to 11pm.- **Entrance Logs** - Records which user accessed which door and when. - Logs remain valid even if related records are updated—e.g., knowing who accessed a door after the user record is deleted.**Tasks:**1. **Design a Conceptual Schema:** - Only includes the six entities. - Focus on designing the relationships.2. **Convert to a Relational Schema:** - Add middle tables for each Many-to-Many (M:N) relationship in the conceptual schema.**Notes:**- Use the designated notation style from Course Project 1 Solution when drawing the diagram.

Answered: Part Design a database for an Access…

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Question

**Part**

**Designing a Database for an Access Control System**

The aim is to manage who has access to which doors at specific times.

**Entities in the Database:**

- **Users**
- **Groups**
- A user can be assigned to more than one group.
- **Doors**
- **AccessRules**
- Determines which group can access which door at which time schedule.
- Example: Group A can access the Front Door on Monday from 7am to 9pm.
- Example: Group B can access the Back Door on Saturday from 8am to 10pm.
- **TimeSchedules**
- Simplified to support only weekly schedules.
- Columns in the TimeSchedules table:
- Id
- DayOfWeek
- StartTime
- EndTime
- Example: A schedule with DayOfWeek=Monday, StartTime=7:00, EndTime=23:00 means access is granted on Monday from 7am to 11pm.
- **Entrance Logs**
- Records which user accessed which door and when.
- Logs remain valid even if related records are updated—e.g., knowing who accessed a door after the user record is deleted.

**Tasks:**

1. **Design a Conceptual Schema:**
- Only includes the six entities.
- Focus on designing the relationships.

2. **Convert to a Relational Schema:**
- Add middle tables for each Many-to-Many (M:N) relationship in the conceptual schema.

**Notes:**
- Use the designated notation style from Course Project 1 Solution when drawing the diagram.

Expert Solution

Step 1

Solution:

Note: The first four subparts have been answered as per Chegg guidelines, please repost others.

10.11)

The objective of the role-based access control system is to control the access mechanisms based on the roles of an individual within the organization. RBAC lets employees have access rights only to the information they need to do their jobs and prevents them from accessing information that doesn't pertain to them.

10.12)

The purpose of backup and recovery is to maintain a copy of the work which is being done based on some rule or protocol which in future can be used for recovery when the system fails and we will have all the work preserved to a particular checkpoint.

10.13)

Database integrity can be compromised and so many different ways and for so many different reasons some of which can be human errors when data is entered, errors that occur when data is transmitted from one computer to another, software bugs or viruses, hardware malfunctions, such as disk crashes and natural disasters, such as fires and floods.

10.14)

The main goal of optimizing database performance is to maintain consistency of the database throughout, minimize the throughput and have a robust system which lasts for a long time.

1. Creating the table Users:

CREATE TABLE Users( UserID int NOT NULL, UserName varchar(50) NOT NULL, GroupID int, PRIMARY KEY (UserID), FOREIGN KEY (GroupId) REFERENCES Groups(GroupId) ); );

2. Creating the table Groups:

CREATE TABLE Groups( GroupId int NOT NULL, GroupName varchar(50) NOT NULL, PRIMARY KEY (GroupId) );

3. Creating the table Rooms:

CREATE TABLE Rooms( RoomId int NOT NULL, RoomName varchar(50) NOT NULL, PRIMARY KEY (RoomId) );

3. Creating the table Access:

CREATE TABLE Access( GroupId int NOT NULL, RoomId int NOT NULL, PRIMARY KEY (RoomId), FOREIGN KEY (RoomId) REFERENCES Rooms(RoomId), FOREIGN KEY (GroupId) REFERENCES Groups(GroupId) );

-------------------------------------------------------

Inserting data into Users table:

1. INSERT INTO Users VALUES (1,' Modesto','1);

2. INSERT INTO Users VALUES (2,' Ayine ',1);

3. INSERT INTO Users VALUES (3,' Christopher',2);

4. INSERT INTO Users VALUES (4,' Cheong',2);

5. INSERT INTO Users VALUES (5,' Saulat',3);

6. INSERT INTO Users VALUES (6,' Heidy', NULL);

Inserting data into Groups table:

1. INSERT INTO Groups VALUES (1,' I.T.' );

2. INSERT INTO Groups VALUES (2,' Sales' );

3. INSERT INTO Groups VALUES (3,' Administration' );

4. INSERT INTO Groups VALUES (4,' Operationes.' );

Inserting data into Rooms table:

1. INSERT INTO Rooms VALUES (1,'101' );

2. INSERT INTO Rooms VALUES (2,'102' );

3. INSERT INTO Rooms VALUES (3,' Auditorium A' );

4. INSERT INTO Rooms VALUES (4,' Auditorium B' );

Inserting data into Access table:

1. INSERT INTO Access VALUES (1,1);

2. INSERT INTO Access VALUES (1,2);

3. INSERT INTO Access VALUES (2,2);

4. INSERT INTO Access VALUES (2,3);

5. INSERT INTO Access VALUES (3,NULL);

-------------------------------------------------------------------------------------------------------

1. Select G.GroupId, G.GroupName, U.UserID from Groups G LEFT JOIN Users U on G.GroupId=U.GroupID;

2. Select R.RoomId, R.RoomName, A.GroupName from Rooms R LEFT JOIN Access A on R.RoomId=A.RoomsID;

3. Select U.UserID, U.UserName, R.RoomName, G.GroupName from User U LEFT JOIN Access on U.GRoupID=A.GroupID LEFT JOIN Rooms R on A.RoomID=R.RoomId order by U.UserName,G.GroupName,R.RoomName;

STEP 1

Solution: