Database fundamentals for information professionals.
Did anybody willing to take my work. Please let me know. I am uploading my Assignment 3 questions. This course is related to database. If anyone ready to take mine, I will share all the lectures and videos provided by the professor.
Thanks&Regards,
Sravanthi.
Page
2
of 5
KENT STATE UNIVERSITY
School of Information
LIS 4/6/8
1
095 Database Fundamentals for Information Professionals
Assignment 3: Relational Database Management System (RDBMS) Essentials
PURPOSES
· To give an example of a Relational Database Management System (RDBMS) on Microsoft Access
· To provide hands-on experience in performing basic database tasks with a RDBMS
INSTRUCTIONS
a) Please watch and complete the course: Access Essential Training (Office 365) in LinkedIn Learning before you start this assignment
(Course link:
https://www.linkedin.com/learning/collections/6577199627504758784?u=76082546
)
Tips to view the lecture:
· You may stop at any point, or repeat any portion.
· Take advantage the transcripts, screen caption, and adjust playing speed as needed.
· You may download the exercise files used in this course to follow the hands-on portion of this course for a better learning experience.
b) RDMS software
· While students can choose from various database applications for hands-on assignments, this course will showcase and provide support for a specific set of hardware and software available to the instructor and ALL students: Microsoft Access.
· If you do not have a database application for hands-on assignments in this course, you may try one of the following two options:
Option 1. If you have a computer running Windows, you may download Microsoft Office 365 freely available from
https://support.kent.edu
. Access is part of Office for Windows.
Option 2. If you only have access to Mac, Office for Macs does NOT include the database software Access. You may use the iSchool-provided virtual desktop with Microsoft Access. Please see the document “Virtual Desktop” for details.
c) NOTE: Please use this assignment file as a template to add your response to each assignment question and submit the completed assignment file via its submission page in the class Blackboard.
EXERCISE 1
Provide screen capture to show you have completed the course “Access Essential Training (Office 365)” in Linkedin Learning
· Below is the screen capture to show I have completed this course, which is displayed on the “overview” tab of this course. You can even download the certificate and add to your Linkedin profile:
·
Please provide your screen capture to show your completion accordingly:
EXERCISE 2
Building a basic database and performing basic database functions
For this exercise, you will use Access or another database program of your choice to implement a database as specified below. Here is a recommended procedure:
· First, create individual tables. For each table, you need to define the properties (metadata) for each field and the primary key of the table.
· Second, create the relationship.
· Third, enter data.
· NOTE: Enter data in primary tables first and related tables second; since you have set up data entry rules, defined the relationship, and established referential integrity, this will help data quality control of data entry.
· Finally, create queries, forms, and reports to facilitate data retrieval.
Specific requirements:
1. Create two tables:
BOOKS
and
PUBLISHERS
, with the exact same fields and records as provided below. For each table, the primary key should be properly defined. In addition, the properties for each field should also be properly defined including data type, length of the field, whether the field is required, etc.
2. The table and field properties should meet the following specifications:
Table |
Field Name |
Data Type |
Description |
Field Size |
Other Properties |
|||||
BOOKS |
ISBN |
Short Text |
Primary key |
13 |
||||||
Title |
20 |
|||||||||
PubID |
Foreign key |
1 | ||||||||
Price |
Currency |
Purchase price |
Format: Currency Decimal places: 2 |
|||||||
PUBLISHERS | ||||||||||
PubName |
15 |
|||||||||
PubPhone |
10 |
Format: XXX-XXX-XXXX |
PUBLISHERS table data: |
BOOKS table data: |
3. The relationship of the two tables in this database should look like the following:
4. Given the data and structure of this database, think about possible useful information that may be generated from this database, and create at least the following required objects as specified in the table below (4 queries, 2 forms, and 2 reports) – feel free (not required) to add any extra objects. We will learn more about queries, forms, and reports later in future modules.
Object |
Query Name in Database |
Description of function and tips |
Query 1 (Required) |
Qry1BooksByPublisher |
Provides a list of all books (with Title, ISBN, and Price) by all publishers sorted by publisher name. We can view all books by each of the three publishers in one place in this query. |
Query 2 (Required) |
Qry2BooksByBigHousePublisher |
Based on Query 1 above, this query only shows books by this particular publisher Big House. Therefore, you need to specify the select criteria in your query. |
Query 3 (Required) |
Qry3CheapBooks |
This query finds all books with a price lower than $25 (not including $25). |
Query 4 (Required) |
Qry4BookCost |
This query is based on the Books table with a column for sale tax at 5%, and another column for the total cost for each book, which is the sum of book price and sales tax. Please refer to the Access training course’s Topic 5/build expressions for related details. You can do this in Access. |
Query 5 (Bonus, not required for this assignment) |
Qry5ParameterByX |
A parameter query – add your specification of your query, and name your query accordingly. |
Query 6 (Bonus, not required for this assignment) |
Qry6BookPriceStats |
A query that provides summary statistics of book prices in this database: count of books, minimum price, maximum price, average price, and total price. |
Form 1 (Required) |
Frm1Xxxx |
A useful data entry form – add your specification and name the form accordingly. |
Form 2 (Required) |
Frm2Yxxx |
A form that can be used for retrieval – add your specification. |
Report 1 (Required) |
rpt1BooksByPublisher |
This basic report lists books grouped and sorted by publisher; each book entry includes title, ISBN, and price. |
Report 2 (Required) |
rpt2BooksByPublisherEnhanced |
Now try to enhance the first report so that this report 1) shows each publisher name only once, 2) has a sum of book prices for each publisher, and 3) has a clear and professional look in terms of line spacing and layout. |
WHAT TO SUBMIT FOR ASSIGNMENT 3:
· Your completed version of this assignment file for both exercises.
· A working Access database file from Exercise 2.
Important note:
If you choose database software other than Access, please record a demo video to show your database and run each of the objects created.
1
2