1. Consider a relation R(A,B,C,D,E,F,G,H) with the following functional dependencies:
A -> BCD
AD -> E
EFG -> H
F -> GH
(a) Based on these functional dependencies, there is one minimal key for R. What is it?
(b) One of the four functional dependencies can be removed without altering the key. Which one?
2. Consider the following relational schema:
UnivInfo(studID, studName, course, profID, profOffice)
Each tuple in relation UnivInfo encodes the fact that the student with the given ID and name took the given course from the professor with the given ID and office. Assume that students have unique IDs but not necessarily unique names, and professors have unique IDs but not necessarily unique offices. Each student has one name; each professor has one office.
(a) Specify a set of completely nontrivial functional dependencies for relation UnivInfo that encodes the assumptions described above and no additional assumptions.
(b) Based on your functional dependencies in part (a), specify all minimal keys for relation UnivInfo.
(c) Is UnivInfo in Boyce-Codd Normal Form (BCNF) according to your answers to (a) and (b)? If not, give a decomposition of UnivInfo into BCNF.
(d) Now add the following two assumptions: (1) No student takes two different courses from the same professor; (2) No course is taught by more than one professor (but a professor may teach more than one course). Specify additional functional dependencies to take these new assumptions into account.
(e) Based on your functional dependencies for parts (a) and (d) together, specify all minimal keys for relation UnivInfo.
(f) Is UnivInfo in BCNF according to your answers to (d) and (e)? If not, give a decomposition of UnivInfo into BCNF.
Category: Databases
4-28. For each of the following E-R diagrams from Chapter 2: Transform the diagr
4-28. For each of the following E-R diagrams from Chapter 2:
Transform the diagr
4-28. For each of the following E-R diagrams from Chapter 2:
Transform the diagram to a relational schema that shows referential integrity constraints (see Figure 4-5 for an example of such a schema).
For each relation, diagram the functional dependencies (see Figure 4-23 for an example).
If any of the relations are not in 3NF, transform them to 3NF.
Figure 2-8
Figure 2-9b
Figure 2-11a
Figure 2-11b
Figure 2-15a (relationship version)
Figure 2-15b (attribute version)
Figure 2-16b
Figure 2-19
4-29. For each of the following EER diagrams from Chapter 3:
Transform the diagram into a relational schema that shows referential integrity constraints (see Figure 4-5 for an example of such a schema).
For each relation, diagram the functional dependencies (see Figure 4-23 for an example).
If any of the relations are not in 3NF, transform them to 3NF.
Figure 3-6b
Figure 3-7a
Figure 3-9
Figure 3-10
Figure 3-11
Create a logical dimensional model (using Lucidchart, draw.io, etc.) of the data
Create a logical dimensional model (using Lucidchart, draw.io, etc.) of the data
Create a logical dimensional model (using Lucidchart, draw.io, etc.) of the dataset (see Instructions section above) based on dimensional modeling principles taught in the lectures and readings.
After creating your model, to submit it you will need to upload a file containing your model from your computer. To save the model you created to your computer, you will need to export it as an image file. To do this in Lucidchart, go to File –> Download As –> PNG, and then save it using a descriptive title. To do this in draw.io, go to File –> Export as –> PNG, and likewise save it using a descriptive title.
Attach File
Summary: To apply all acquired knowledge in the last Project Assignments to crea
Summary:
To apply all acquired knowledge in the last Project Assignments to crea
Summary:
To apply all acquired knowledge in the last Project Assignments to create a Database Scheme. For this project, the ERD is provided to you.
Deliverables:
SQL Script with your name as follows: YourLastName_Project_Ph3.sql.
MS Word Document with your OUTPUT, named as follows: YourLastName_Project_Ph3_Output.doc.
ZIP file to include the two files mentioned above.
Required Software:
MySQL DBMS by Oracle. MySQL Community Server and MySQL Workbench.
Follow Step 1-3 for your Course Project Phase phase 1, to install the required software components.
Lab Steps:
Step 1: Analyze the ERD for a student information system provided below. Be alert for the specifications provided for: 1) Entities, 2) Attributes, 3) Primary Key, and 4) Relationships. You will need to have a clear understanding for these components to create your database using MySQL.
Step 2: Create a New Database in MySQL, Produce SQL File, Drop Table
Create a new Database, as you did in your Course Project Phase 1 deliverable and you already created a database using the MySQL DBMS. Reference the attached video (Course Project Ph1 Video.mp4), for a demonstration to create a new Database using MySQL. This video was initially presented to you for phase 2 of your project. Also, reference the supplemental text: Available for Free download as part of our UC Library. MySQL Database Usage & Administration. (2010). By Vaswani, Vikram. McGraw Hill. ISBN: 978-0-07-160550-2. Link to UC Library: http://search.ebscohost.com/login.aspx?direct=true…
Utilize the SQL dialect you learned so far in this course for MySQL. Use the file supplemental textbook: MySQL Database Usage & Administration. (2010). By Vaswani, Vikram. McGraw Hill, as supplement your knowledge of the MySQL dialect.
Create your SCRIPT file to be named: YourLastName_Project_Ph3.sql.
Step 3: Include the following commands at the tops of your scripts:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS STUDENT;
DROP TABLE IF EXISTS CAMPUS;
DROP TABLE IF EXISTS ROOM;
DROP TABLE IF EXISTS COURSE;
DROP TABLE IF EXISTS INSTRUCTOR;
DROP TABLE IF EXISTS APPROVED_INSTRUCTOR;
DROP TABLE IF EXISTS CLASS;
DROP TABLE IF EXISTS STUDENT_GRADE;
SET FOREIGN_KEY_CHECKS=1;
Step 4: Create Tables in your database
Create a new Table for each of the entities provided to you in the ERD diagram in Step 1.
Add a column to represent each attribute addressed in the ERD diagram in Step 1.
Designate Primary Key, as noted in the ERD diagram in Step 1.
Designate a Foreign Key relationship between the tables, as noted in the ERD diagram in Step 1.
Enable referential integrity on the relationships, as needed.
Enable cascade updates as needed on the relationships.
Step 5: Designate Data Types. Update the data type as needed to enforce the domain constrain of the data. This needs to be completed for every column for all tables.
Dates: they should have a date data designation type.
Surrogate keys: shall be auto-numbered
Character type: shall have a character data designation type.
Step 6: Column Constraints Designation.
Grade designation must be of one of these values as follows: A, B, C, D, E, F, W, E (E=enrolled, and W = withdrawn).
Student’s first and last names are not to be designated as NULL.
Course Credit hours shall be BETWEEN one and four.
The instructor first and last name must NOT be NULL
Course name designation has to be UNIQUE and must not be NULL type.
Step 7: Data Table Addition
Use the INSERT operator to add minimum 2-3 rows of data per each database.
You are free to use any values you might like for each of the columns.
NOTE/ Reminder: you are required to add data to the parent table prior to adding any data to child tables, as referential integrity is enabled.
Step 8: Executing your SCRIPT
Must incorporate the COMMIT command at the end of your Script
Must incorporate the SHOW TABLES command, to display the table you created. At the end of the script created.
Must incorporate the SELECT statement to show data allocated for each table. This would be added at the end of the script.
EXECUTE your SCRIPT.
Copy and paste your OUTPUT into your MS Word file as follows: YourLastName_Project_Ph3_Output.doc
Step 9: Upload your work
Upload ZIP file to include the two files as follows: 1) SQL Script with your name as follows: YourLastName_Project_Ph3.sql, and 2) MS Word Document with your OUTPUT, named as follows: YourLastName_Project_Ph3_Output.doc.
Rubric:
NOTE: Please include good documentations.
Tables Created: create a table for each entity as noted in the ERD diagram in Step 1. 20 points
Columns Created: create a column for each attribute as noted the ERD diagram in Step 1. 20 points
Primary Key designation: primary key addressed for all tables with unique constraints specified as column properties. 20 points
Data Types: Addressed for each attribute. Should include: 1) Date data type incorporated, 2) Surrogate Key Automated, 3) Numeric data shall be numeric type, 4) Character data shall have a character type. 20 points
Relationships Created, as noted in the ERD diagram in STEP 1. Relationships shall be enabled for referential integrity and cascade updates. 40 points
Data Added per row: 5 rows of data for each table. 40 points
Column Constraints Added: As noted in the description for the project. Checked by adding rows with invalid values. Should take into consideration constraints as follows: 1) student name cannot be NULL, 2) course credit hours are restricted to allocations between 1-4, 3) course name is unique and NOT NULL, 4) instructor last name and first name can NOT be NULL, and 5) grade restrictions to the following values: A,B,C,D,F,I,W, E. 40 points
Summary: To apply all acquired knowledge in the last Project Assignments to crea
Summary:
To apply all acquired knowledge in the last Project Assignments to crea
Summary:
To apply all acquired knowledge in the last Project Assignments to create a Database Scheme. For this project, the ERD is provided to you.
Deliverables:
SQL Script with your name as follows: YourLastName_Project_Ph3.sql.
MS Word Document with your OUTPUT, named as follows: YourLastName_Project_Ph3_Output.doc.
ZIP file to include the two files mentioned above.
Required Software:
MySQL DBMS by Oracle. MySQL Community Server and MySQL Workbench.
Follow Step 1-3 for your Course Project Phase phase 1, to install the required software components.
Lab Steps:
Step 1: Analyze the ERD for a student information system provided below. Be alert for the specifications provided for: 1) Entities, 2) Attributes, 3) Primary Key, and 4) Relationships. You will need to have a clear understanding for these components to create your database using MySQL.
Step 2: Create a New Database in MySQL, Produce SQL File, Drop Table
Create a new Database, as you did in your Course Project Phase 1 deliverable and you already created a database using the MySQL DBMS. Reference the attached video (Course Project Ph1 Video.mp4), for a demonstration to create a new Database using MySQL. This video was initially presented to you for phase 2 of your project. Also, reference the supplemental text: Available for Free download as part of our UC Library. MySQL Database Usage & Administration. (2010). By Vaswani, Vikram. McGraw Hill. ISBN: 978-0-07-160550-2. Link to UC Library: http://search.ebscohost.com/login.aspx?direct=true…
Utilize the SQL dialect you learned so far in this course for MySQL. Use the file supplemental textbook: MySQL Database Usage & Administration. (2010). By Vaswani, Vikram. McGraw Hill, as supplement your knowledge of the MySQL dialect.
Create your SCRIPT file to be named: YourLastName_Project_Ph3.sql.
Step 3: Include the following commands at the tops of your scripts:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS STUDENT;
DROP TABLE IF EXISTS CAMPUS;
DROP TABLE IF EXISTS ROOM;
DROP TABLE IF EXISTS COURSE;
DROP TABLE IF EXISTS INSTRUCTOR;
DROP TABLE IF EXISTS APPROVED_INSTRUCTOR;
DROP TABLE IF EXISTS CLASS;
DROP TABLE IF EXISTS STUDENT_GRADE;
SET FOREIGN_KEY_CHECKS=1;
Step 4: Create Tables in your database
Create a new Table for each of the entities provided to you in the ERD diagram in Step 1.
Add a column to represent each attribute addressed in the ERD diagram in Step 1.
Designate Primary Key, as noted in the ERD diagram in Step 1.
Designate a Foreign Key relationship between the tables, as noted in the ERD diagram in Step 1.
Enable referential integrity on the relationships, as needed.
Enable cascade updates as needed on the relationships.
Step 5: Designate Data Types. Update the data type as needed to enforce the domain constrain of the data. This needs to be completed for every column for all tables.
Dates: they should have a date data designation type.
Surrogate keys: shall be auto-numbered
Character type: shall have a character data designation type.
Step 6: Column Constraints Designation.
Grade designation must be of one of these values as follows: A, B, C, D, E, F, W, E (E=enrolled, and W = withdrawn).
Student’s first and last names are not to be designated as NULL.
Course Credit hours shall be BETWEEN one and four.
The instructor first and last name must NOT be NULL
Course name designation has to be UNIQUE and must not be NULL type.
Step 7: Data Table Addition
Use the INSERT operator to add minimum 2-3 rows of data per each database.
You are free to use any values you might like for each of the columns.
NOTE/ Reminder: you are required to add data to the parent table prior to adding any data to child tables, as referential integrity is enabled.
Step 8: Executing your SCRIPT
Must incorporate the COMMIT command at the end of your Script
Must incorporate the SHOW TABLES command, to display the table you created. At the end of the script created.
Must incorporate the SELECT statement to show data allocated for each table. This would be added at the end of the script.
EXECUTE your SCRIPT.
Copy and paste your OUTPUT into your MS Word file as follows: YourLastName_Project_Ph3_Output.doc
Step 9: Upload your work
Upload ZIP file to include the two files as follows: 1) SQL Script with your name as follows: YourLastName_Project_Ph3.sql, and 2) MS Word Document with your OUTPUT, named as follows: YourLastName_Project_Ph3_Output.doc.
Rubric:
NOTE: Please include good documentations.
Tables Created: create a table for each entity as noted in the ERD diagram in Step 1. 20 points
Columns Created: create a column for each attribute as noted the ERD diagram in Step 1. 20 points
Primary Key designation: primary key addressed for all tables with unique constraints specified as column properties. 20 points
Data Types: Addressed for each attribute. Should include: 1) Date data type incorporated, 2) Surrogate Key Automated, 3) Numeric data shall be numeric type, 4) Character data shall have a character type. 20 points
Relationships Created, as noted in the ERD diagram in STEP 1. Relationships shall be enabled for referential integrity and cascade updates. 40 points
Data Added per row: 5 rows of data for each table. 40 points
Column Constraints Added: As noted in the description for the project. Checked by adding rows with invalid values. Should take into consideration constraints as follows: 1) student name cannot be NULL, 2) course credit hours are restricted to allocations between 1-4, 3) course name is unique and NOT NULL, 4) instructor last name and first name can NOT be NULL, and 5) grade restrictions to the following values: A,B,C,D,F,I,W, E. 40 points
Summary: To apply all acquired knowledge in the last Project Assignments to crea
Summary:
To apply all acquired knowledge in the last Project Assignments to crea
Summary:
To apply all acquired knowledge in the last Project Assignments to create a Database Scheme. For this project, the ERD is provided to you.
Deliverables:
SQL Script with your name as follows: YourLastName_Project_Ph3.sql.
MS Word Document with your OUTPUT, named as follows: YourLastName_Project_Ph3_Output.doc.
ZIP file to include the two files mentioned above.
Required Software:
MySQL DBMS by Oracle. MySQL Community Server and MySQL Workbench.
Follow Step 1-3 for your Course Project Phase phase 1, to install the required software components.
Lab Steps:
Step 1: Analyze the ERD for a student information system provided below. Be alert for the specifications provided for: 1) Entities, 2) Attributes, 3) Primary Key, and 4) Relationships. You will need to have a clear understanding for these components to create your database using MySQL.
Step 2: Create a New Database in MySQL, Produce SQL File, Drop Table
Create a new Database, as you did in your Course Project Phase 1 deliverable and you already created a database using the MySQL DBMS. Reference the attached video (Course Project Ph1 Video.mp4), for a demonstration to create a new Database using MySQL. This video was initially presented to you for phase 2 of your project. Also, reference the supplemental text: Available for Free download as part of our UC Library. MySQL Database Usage & Administration. (2010). By Vaswani, Vikram. McGraw Hill. ISBN: 978-0-07-160550-2. Link to UC Library: http://search.ebscohost.com/login.aspx?direct=true…
Utilize the SQL dialect you learned so far in this course for MySQL. Use the file supplemental textbook: MySQL Database Usage & Administration. (2010). By Vaswani, Vikram. McGraw Hill, as supplement your knowledge of the MySQL dialect.
Create your SCRIPT file to be named: YourLastName_Project_Ph3.sql.
Step 3: Include the following commands at the tops of your scripts:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS STUDENT;
DROP TABLE IF EXISTS CAMPUS;
DROP TABLE IF EXISTS ROOM;
DROP TABLE IF EXISTS COURSE;
DROP TABLE IF EXISTS INSTRUCTOR;
DROP TABLE IF EXISTS APPROVED_INSTRUCTOR;
DROP TABLE IF EXISTS CLASS;
DROP TABLE IF EXISTS STUDENT_GRADE;
SET FOREIGN_KEY_CHECKS=1;
Step 4: Create Tables in your database
Create a new Table for each of the entities provided to you in the ERD diagram in Step 1.
Add a column to represent each attribute addressed in the ERD diagram in Step 1.
Designate Primary Key, as noted in the ERD diagram in Step 1.
Designate a Foreign Key relationship between the tables, as noted in the ERD diagram in Step 1.
Enable referential integrity on the relationships, as needed.
Enable cascade updates as needed on the relationships.
Step 5: Designate Data Types. Update the data type as needed to enforce the domain constrain of the data. This needs to be completed for every column for all tables.
Dates: they should have a date data designation type.
Surrogate keys: shall be auto-numbered
Character type: shall have a character data designation type.
Step 6: Column Constraints Designation.
Grade designation must be of one of these values as follows: A, B, C, D, E, F, W, E (E=enrolled, and W = withdrawn).
Student’s first and last names are not to be designated as NULL.
Course Credit hours shall be BETWEEN one and four.
The instructor first and last name must NOT be NULL
Course name designation has to be UNIQUE and must not be NULL type.
Step 7: Data Table Addition
Use the INSERT operator to add minimum 2-3 rows of data per each database.
You are free to use any values you might like for each of the columns.
NOTE/ Reminder: you are required to add data to the parent table prior to adding any data to child tables, as referential integrity is enabled.
Step 8: Executing your SCRIPT
Must incorporate the COMMIT command at the end of your Script
Must incorporate the SHOW TABLES command, to display the table you created. At the end of the script created.
Must incorporate the SELECT statement to show data allocated for each table. This would be added at the end of the script.
EXECUTE your SCRIPT.
Copy and paste your OUTPUT into your MS Word file as follows: YourLastName_Project_Ph3_Output.doc
Step 9: Upload your work
Upload ZIP file to include the two files as follows: 1) SQL Script with your name as follows: YourLastName_Project_Ph3.sql, and 2) MS Word Document with your OUTPUT, named as follows: YourLastName_Project_Ph3_Output.doc.
Rubric:
NOTE: Please include good documentations.
Tables Created: create a table for each entity as noted in the ERD diagram in Step 1. 20 points
Columns Created: create a column for each attribute as noted the ERD diagram in Step 1. 20 points
Primary Key designation: primary key addressed for all tables with unique constraints specified as column properties. 20 points
Data Types: Addressed for each attribute. Should include: 1) Date data type incorporated, 2) Surrogate Key Automated, 3) Numeric data shall be numeric type, 4) Character data shall have a character type. 20 points
Relationships Created, as noted in the ERD diagram in STEP 1. Relationships shall be enabled for referential integrity and cascade updates. 40 points
Data Added per row: 5 rows of data for each table. 40 points
Column Constraints Added: As noted in the description for the project. Checked by adding rows with invalid values. Should take into consideration constraints as follows: 1) student name cannot be NULL, 2) course credit hours are restricted to allocations between 1-4, 3) course name is unique and NOT NULL, 4) instructor last name and first name can NOT be NULL, and 5) grade restrictions to the following values: A,B,C,D,F,I,W, E. 40 points
Deliverable Submit upto 5 scenarios that we can model in class. Each correct sce
Deliverable
Submit upto 5 scenarios that we can model in class. Each correct sce
Deliverable
Submit upto 5 scenarios that we can model in class. Each correct scenario is worth one point. If you submit 5 scenarios you get all the points. You must have 3 or more entities in your scenario to get credit. Note I am looking for the narrative and a ERD.
Note: You may have two, narratives that translate into to entities. The remaining three narratives must have 3 or more entities.
For Credit
You must create the ERD using Lucid Chart or Smart Draw
Example of an acceptable Narrative
Here is an example of what I am looking for: A soccer player is assigned to a team. Each team is assigned a coach.
Please read her how to create ERD.
https://www.lucidchart.com/pages/how-to-draw-ERD
discuss a personal example where Big Data Analytics has positively (or negativel
discuss a personal example where Big Data Analytics has positively (or negativel
discuss a personal example where Big Data Analytics has positively (or negatively) affected YOU in some way. Relate your experience to the concepts that you have learned in the class.
this project takes place in saudi arabia, Riyadh. if any names or areas or quest
this project takes place in saudi arabia, Riyadh. if any names or areas or quest
this project takes place in saudi arabia, Riyadh. if any names or areas or question about culture / student please ask so i can provide it for you.
– the codes should be written as well as screenshot
– regarding the screen shot can you provide and extra zip file with all the pictures.
thanks 🙂
City Tours Ltd. ADMINISTERING A DATABASE SYSTEM GETTING STARTED Save th
City Tours Ltd.
ADMINISTERING A DATABASE SYSTEM
GETTING STARTED
Save th
City Tours Ltd.
ADMINISTERING A DATABASE SYSTEM
GETTING STARTED
Save the file SC_AC365_2021_9b_FirstLastName_1.accdb as SC_AC365_2021_9b_FirstLastName_2.accdb Edit the file name by changing “1” to “2”.
If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.
To complete this Project, you will also need the following files:Support_AC365_2021_9b_TourTypes.accdb
With the file SC_AC365_2021_9b_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table. If the table does not display your name, delete the file and download a new copy.
PROJECT STEPS
City Tours Ltd. is a company that provides tours in a city that attracts tourists from around the country. As the customer service manager for the company, you need to understand database administration tasks.
Open the database in Backstage View and then click the View and edit database properties link. When the Properties dialog box displays, create a custom property for the database as described below:
With the database still open in Backstage View, click the Options tab. Select the Main Menu Navigation Form as the Display Form for the database as shown in Figure 2. (Hint: The Display Form is sometimes referred to as the startup form.) Click Ok. (Hint: When the Access dialog box appears and displays the message, “You must close and reopen the current database for the specified option to take effect”, click the OK button.)
Open the Tours table in Design View, and then create a custom input mask for the TourNumber field as described below:The TourNumber field must consist of one letter and three numbers.
The letter should display in upper case. (Hint: Enter >L999 as the input mask.)
Save the change to the table and then close it.
Open the Guides table in Design View and modify the GuideName multiple-field index by completing the following tasks:Open the Indexes dialog box.
Change the Sort Order for the LastName field to Descending.
Save the changes to the Guides table and then close it.
Open the Customers table in Design View, and then create a single-field index on the LastName field. The index should allow duplicate values. Save the changes to the table design but do not close the table.
With the Customers table still open in Design View, create a multiple-field index as described below: Use Address as the name of the index. (Hint: Be careful not to include spaces after the index name.)
Use the State field, sorted in Descending order, as the first field in the index.
Use the City field, sorted in Ascending order, as the second field in the index.
Save the changes to the Customers table and then close it.
Open the SegwayTours table in Design View, and then open the Property Sheet for the table and make the following updates:Create a validation rule for the table requiring that the Paid field value is always less than or equal to the TotalCost field value. (Hint: Enter [Paid]<=[TotalCost] as the validation rule.)
Enter Paid cannot exceed TotalCost as the validation text.
Close the Property Sheet, and then save the changes to the table. (Hint: Because there was a change to data integrity rules, the "existing data may not be valid" warning message will appear. Click No and continue saving the table.)
With the SegwayTours table still open in Design View, create a multiple-field index as described below:Use TourInformation as the name of the index. (Hint: Be careful not to include spaces after the index name.)
Use the CustomerID field, sorted in Ascending order, as the first field in the index.
Use the TourNumber field, sorted in Ascending order, as the second field in the index.
Save the changes to the SegwayTours table, and then close it.
Create a Datasheet form for the HomeTours table, and then save the form using HomeToursForm as the form name. Close the form.
Create a blank form based on the 1 Right application part. (Hint: The blank form will appear in the Navigation Pane as a forms object named SingleOneColumnRightLabels and is saved automatically.)
Import a table from an encrypted database by completing the following tasks:Open the encrypted database Support_AC365_2021_9b_TourTypes.accdb in Exclusive mode, using the password tours when the Password Required dialog box appears. Enable the content.
Decrypt the Support_AC365_2021_9b_TourTypes.accdb database. (Hint: You will need to use the password tours again in the Unset Database Password dialog box.) Close the database.
Open (or return to) the SC_AC365_2021_9b_FirstLastName_2.accdb database.
Import the TourTypes table from the database Support_AC365_2021_9b_TourTypes.accdb into a new table using TourTypes as the table name. (Hint: Ensure that the Tables tab is selected in the Import Objects dialog box.)
Do not save the Import steps.
Use the Table Analyzer to analyze the Customers table for redundancy. Implement the following options when using the Table Analyzer Wizard:Select the Customers table as the table to analyze, and let the wizard decide what fields go in each table.
Rename Table 1 as AltCustomers using the Rename table button as shown in Figure 3.
Rename Table 2 as AltRegions using the Rename table button as shown in Figure 3.
When the wizard displays the next screen with the question, "do the bold fields uniquely identify each record in the proposed table", make sure the Generated Unique ID field is the primary key of the AltRegions table as shown in Figure 3. Then, click the Next button.
Ensure that the "No, don't create a query" radio button is selected, then click Finish to close the Table Analyzer Wizard.
When the Table Analyzer Wizard dialog box appears with the message, "The command or action TileHorizontally isn't available now", click OK. Close the AltCustomers and AltRegions tables.
Confirm the Navigation Pane matches Figure 4.
Switch to viewing database items by the custom category named "Guide Information" in the Navigation Pane. Add the following two reports to the Guides Information Reports group: Basic Guide List
Guide Skills Report
Confirm the Guide Information Reports group matches Figure 5.
Add a new group to the Guide Information category as described below:Use Guide Information Forms as the name for the new group.
If necessary, move the Guide Information Forms group so that it appears immediately before the Guide Information Reports.
In the Navigation Pane, add the Guides Update Form to the new group.
Confirm the Guide Information Forms group and the Guides Update Form item in the Navigation Pane match Figure 5.
Figure 1: Custom Properties Tab in the Properties Dialog Box
Figure 2: Access Options Dialog Box
Figure 3: Table Analyzer Wizard
Figure 4: Navigation Pane with Tables Created in Step 12
Figure 5: Navigation Pane Viewed by Guide Information Category
Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the website to submit your completed project.Shelly Cashman Access 365/2021 | Module 9: SAM Project 1b
City Tours Ltd.
ADMINISTERING A DATABASE SYSTEM
GETTING STARTED
Save the file SC_AC365_2021_9b_FirstLastName_1.accdb as SC_AC365_2021_9b_FirstLastName_2.accdb Edit the file name by changing “1” to “2”.
If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.
To complete this Project, you will also need the following files:Support_AC365_2021_9b_TourTypes.accdb
With the file SC_AC365_2021_9b_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table. If the table does not display your name, delete the file and download a new copy.
PROJECT STEPS
City Tours Ltd. is a company that provides tours in a city that attracts tourists from around the country. As the customer service manager for the company, you need to understand database administration tasks.
Open the database in Backstage View and then click the View and edit database properties link. When the Properties dialog box displays, create a custom property for the database as described below:
With the database still open in Backstage View, click the Options tab. Select the Main Menu Navigation Form as the Display Form for the database as shown in Figure 2. (Hint: The Display Form is sometimes referred to as the startup form.) Click Ok. (Hint: When the Access dialog box appears and displays the message, "You must close and reopen the current database for the specified option to take effect", click the OK button.)
Open the Tours table in Design View, and then create a custom input mask for the TourNumber field as described below:The TourNumber field must consist of one letter and three numbers.
The letter should display in upper case. (Hint: Enter >L999 as the input mask.)
Save the change to the table and then close it.
Open the Guides table in Design View and modify the GuideName multiple-field index by completing the following tasks:Open the Indexes dialog box.
Change the Sort Order for the LastName field to Descending.
Save the changes to the Guides table and then close it.
Open the Customers table in Design View, and then create a single-field index on the LastName field. The index should allow duplicate values. Save the changes to the table design but do not close the table.
With the Customers table still open in Design View, create a multiple-field index as described below: Use Address as the name of the index. (Hint: Be careful not to include spaces after the index name.)
Use the State field, sorted in Descending order, as the first field in the index.
Use the City field, sorted in Ascending order, as the second field in the index.
Save the changes to the Customers table and then close it.
Open the SegwayTours table in Design View, and then open the Property Sheet for the table and make the following updates:Create a validation rule for the table requiring that the Paid field value is always less than or equal to the TotalCost field value. (Hint: Enter [Paid]<=[TotalCost] as the validation rule.)
Enter Paid cannot exceed TotalCost as the validation text.
Close the Property Sheet, and then save the changes to the table. (Hint: Because there was a change to data integrity rules, the "existing data may not be valid" warning message will appear. Click No and continue saving the table.)
With the SegwayTours table still open in Design View, create a multiple-field index as described below:Use TourInformation as the name of the index. (Hint: Be careful not to include spaces after the index name.)
Use the CustomerID field, sorted in Ascending order, as the first field in the index.
Use the TourNumber field, sorted in Ascending order, as the second field in the index.
Save the changes to the SegwayTours table, and then close it.
Create a Datasheet form for the HomeTours table, and then save the form using HomeToursForm as the form name. Close the form.
Create a blank form based on the 1 Right application part. (Hint: The blank form will appear in the Navigation Pane as a forms object named SingleOneColumnRightLabels and is saved automatically.)
Import a table from an encrypted database by completing the following tasks:Open the encrypted database Support_AC365_2021_9b_TourTypes.accdb in Exclusive mode, using the password tours when the Password Required dialog box appears. Enable the content.
Decrypt the Support_AC365_2021_9b_TourTypes.accdb database. (Hint: You will need to use the password tours again in the Unset Database Password dialog box.) Close the database.
Open (or return to) the SC_AC365_2021_9b_FirstLastName_2.accdb database.
Import the TourTypes table from the database Support_AC365_2021_9b_TourTypes.accdb into a new table using TourTypes as the table name. (Hint: Ensure that the Tables tab is selected in the Import Objects dialog box.)
Do not save the Import steps.
Use the Table Analyzer to analyze the Customers table for redundancy. Implement the following options when using the Table Analyzer Wizard:Select the Customers table as the table to analyze, and let the wizard decide what fields go in each table.
Rename Table 1 as AltCustomers using the Rename table button as shown in Figure 3.
Rename Table 2 as AltRegions using the Rename table button as shown in Figure 3.
When the wizard displays the next screen with the question, "do the bold fields uniquely identify each record in the proposed table", make sure the Generated Unique ID field is the primary key of the AltRegions table as shown in Figure 3. Then, click the Next button.
Ensure that the "No, don't create a query" radio button is selected, then click Finish to close the Table Analyzer Wizard.
When the Table Analyzer Wizard dialog box appears with the message, "The command or action TileHorizontally isn't available now", click OK. Close the AltCustomers and AltRegions tables.
Confirm the Navigation Pane matches Figure 4.
Switch to viewing database items by the custom category named "Guide Information" in the Navigation Pane. Add the following two reports to the Guides Information Reports group: Basic Guide List
Guide Skills Report
Confirm the Guide Information Reports group matches Figure 5.
Add a new group to the Guide Information category as described below:Use Guide Information Forms as the name for the new group.
If necessary, move the Guide Information Forms group so that it appears immediately before the Guide Information Reports.
In the Navigation Pane, add the Guides Update Form to the new group.
Confirm the Guide Information Forms group and the Guides Update Form item in the Navigation Pane match Figure 5.
Figure 1: Custom Properties Tab in the Properties Dialog Box
Figure 2: Access Options Dialog Box
Figure 3: Table Analyzer Wizard
Figure 4: Navigation Pane with Tables Created in Step 12
Figure 5: Navigation Pane Viewed by Guide Information Category
Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the website to submit your completed project.