To create a new database, open your database management system (e.g., Microsoft Access). Select the option to…

Question

To create a new database, open your database management system (e.g., Microsoft Access). Select the option to create a new blank database, then specify the file name as StudDatabase and save it in y

ScanSolve AI Tutor · Accepted Answer

Step 2: Create a database named StudDatabase. To create a new database, open your database management system (e.g., Microsoft Access). Select the option to create a new blank database, then specify the file name as StudDatabase and save it in your designated candidate folder. Step 3: Import the spreadsheet named Practical. In your database management system, navigate to the 'External Data' tab or equivalent. Select 'New Data Source' and choose 'From File', then 'Excel'. Browse to your candidate folder, select the StudentsInfo workbook, and then choose the Practical spreadsheet. Follow the wizard to import the data, ensuring that the first row contains column headers and that no extra fields are added. Step 4: Create a query to extract male students from LAB 1 or LAB 2, grouped by subject. To achieve this, you would use a SQL query. &SELECT StudNo, Surname, Other Name, Sex, SubCode, SubName, Lab.No &FROM Practical &WHERE Sex = 'M' AND (Lab.No = 'LAB 1' OR Lab.No = 'LAB 2') &GROUP BY SubName, StudNo, Surname, Other Name, Sex, SubCode, Lab.No &ORDER BY SubName; Adjust the field widths in the query design view to accommodate the maximum data length for each field. Save the query as MaleLab1. Step 5: Give the two possible key fields of the table above. The original Practical table has two possible candidate keys that can uniquely identify each record: 1. (StudNo, SubCode, Lab.No): This composite key uses all three fields to ensure uniqueness. 2. (StudNo, Lab.No): This composite key also uniquely identifies each record in the table. Step 6: Create the normalized tables Students, Subjects, and LabWork. 1. Create Students table: Copy the Practical table. Delete the SubCode, SubName, and Lab.No fields. Remove any duplicate records based on StudNo. Rename the table to Students. The primary key will be StudNo. 2. Create Subjects table: Copy the Practical table. Delete the StudNo, Surname, Other Name, Sex, and Lab.No fields. Remove any duplicate records based on SubCode. Rename the table to Subjects. The primary key will be SubCode. 3. Create LabWork table: Copy the Practical table. Delete the Surname, Other Name, Sex, and SubName fields. Remove any duplicate records based on the composite key (StudNo, SubCode, Lab.No). Rename the table to LabWork. The composite primary key will be (StudNo, SubCode, Lab.No). Step 7: Create a relationship in the database from the three created tables. Establish relationships between the tables using primary and foreign keys: Create a one-to-many relationship between Students and LabWork by linking Students.StudNo (primary key) to LabWork.StudNo (foreign key). Create a one-to-many relationship between Subjects and LabWork by linking Subjects.SubCode (primary key) to LabWork.SubCode (foreign key). Ensure referential integrity is enforced for these relationships.

To create a new database, open your database management system (e.g., Microsoft Access). Select the option to create a new blank database, then specify the file name as StudDatabase and save it in y

Step-by-step solution

Create a database named `StudDatabase`.

Import the spreadsheet named `Practical`.

Create a query to extract male students from LAB 1 or LAB 2, grouped by subject.

Give the two possible key fields of the table above.

Create the normalized tables `Students`, `Subjects`, and `LabWork`.

Create a relationship in the database from the three created tables.

Still stuck on this one?

More Business/Management Questions