Create database that contains the inventory of organization

Assignment Help Database Management System

Reference no: EM13896464

Create a database that contains the inventory of your organization's technology, as well a table including employee information. Build your database named IT_inventory, linking users to workstations, i.e., each computer (desktop or notebook) will be assigned to a particular employee. You will need to create an Employee table with appropriate fields within the IT_inventory, Access database.
The Employeetable will be joined, through the employee ID field, to the Desktop and Laptop tables.

Your IT_inventory database will include the following tables (including one query and one report, generated after all data and tables are complete), fields and field properties:

Tables (5)

• Desktop
• Employee
• Laptop
• Network (includes switches, router, and printers )
• Server

Query and report (2)

• Desktop/laptop (query)
• Desktop/laptop (report)

a) TABLES:

Employee Table

Create a table called Employees that will be linked to the Desktop workstation and Laptop tables by Employee ID. Again, only workstations and laptops will be linked to an employee (not servers, printers, etc.).Make sure that Employee ID field name uses Short Text as a Data Type. Once fields are created, you use "External Data" to append and add records from Employee Sheet in the RFP_Project.xlsx file to populate your table and cut-and-pastelocation from the Employees and Room numbersSheet in the same RFP_Project.xlsx file.

Create these fields:
• Employee ID
• Lastname
• First name
• Location

◊ LaptopTable

*Note*: ALL fields will use "Short Text" as data types.
FIELD NAME	FIELD INSTRUCTIONS
Device ID	Use Lookup Table using existing table, "Employees" Follow these steps to create your lookup: o Use Lookup Table and select: "I want the field to get the values from another table or query." o Select Table: Employee o Select Field: Last Name o Press Next o Select Last Name Ascending o Press Next o Uncheck "Hide key column (recommended)" o Select Last Name Column and click and drag it to the left of the Employee ID column o Press Finish o SELECT NO WHEN ASKED TO SAVE THE TABLE AT THIS TIME Show Device ID plus the last name of the employee assigned to the computer, i.e. "LT-Smith" o Format the "Field Properties" so that the prefix for the type of device ("LT-" for laptop) appears at the very left of the entry is followed by the employee name by adding a lookup table from the *Employees* table, e.g. LT-Smith Hint: In the Field Properties for Device ID use Format: !"LT-"
Description	Use a Look Up table Select: "I will type in the values that I want" Enter: o Web Server o Developer's Workstation Press Finish
Vendor	Use a Look Up table Select: "I will type in the values that I want" Enter: o Dell o HP o Apple Press Finish
Operating System	Use a Look Up table Select: "I will type in the values that I want" Enter: o Windows 7 o OSX o Linux Press Finish
Employee ID	Use Lookup Table using existing table, "Employees" Follow these steps to create your lookup: o Use Lookup Table and select: "I want the field to get the values from another table or query." o Select Table: Employee o Select Field: Last Name o Press Next o Select Last Name Ascending o Press Next o Uncheck "Hide key column (recommended)" o Press Finish o SELECT NO WHEN ASKED TO SAVE THE TABLE AT THIS TIME
Close and press "Yes" to save the table

◊ DesktopTable

*Note*: ALL fields will use "Short Text" as data types.
FIELD NAME	FIELD INSTRUCTIONS
Device ID	Use Lookup Table using existing table, "Employees" Follow these steps to create your lookup: o Use Lookup Table and select: "I want the field to get the values from another table or query." o Select Table: Employee o Select Field: Last Name o Press Next o Select Last Name Ascending o Press Next o Uncheck "Hide key column (recommended)" o Select Last Name Column and click and drag it to the left of the Employee ID column o Press Finish o SELECT NO WHEN ASKED TO SAVE THE TABLE AT THIS TIME Show Device ID plus the last name of the employee assigned to the computer, i.e. "DT-Smith" o Format the "Field Properties" so that the prefix for the type of device ("DT-" for laptop) appears at the very left of the entry is followed by the employee name by adding a lookup table from the *Employees* table, e.g. DT-Smith Hint: In the Field Properties for Device ID use Format: !"DT-"
Description	Use a Look Up table Select: "I will type in the values that I want" Enter: o Web Server o Developer's Workstation Press Finish
Vendor	Use a Look Up table Select: "I will type in the values that I want" Enter: o Dell o HP o Apple Press Finish
Operating System	Use a Look Up table Select: "I will type in the values that I want" Enter: o Windows 7 o OSX o Linux Press Finish
Employee ID	Use Lookup Table using existing table, "Employees" Follow these steps to create your lookup: o Use Lookup Table and select: "I want the field to get the values from another table or query." o Select Table: Employee o Select Field: Last Name o Press Next o Select Last Name Ascending o Press Next o Uncheck "Hide key column (recommended)" o Press Finish o SELECT NO WHEN ASKED TO SAVE THE TABLE AT THIS TIME
Close and press "Yes" to save the table

SETTING THE TABLE JOINS

• Select DATABASE TOOLS then Relationships
• Make sure Employee, Desktop, and Laptop Table are visible

• Join Table:EmployeesField:Employee ID to Table:Desktop Computers Field:Employee ID
• Check "Enforce Referential Integrity"
• Click "Join Type" and select the second option (Left Outside Join)
• Press Create

• Join Table:EmployeesField:Employee ID to Table:Laptop Computers Field:Employee ID
• Check "Enforce Referential Integrity"
• Click "Join Type" and select the second option (Left Outside Join)
• Press Create

◊ NetworkTable

Create these fields:
• Device ID
• Description
• Vendor
• IP number
o Field property: format @@\.@@\.@@\.@@ (may need to adjust for number)
o IP number data type: text
• Location

◊ ServerTable

Create these fields:
• Device ID, simply enter "S1" or "S2" - you do not need to format or create a lookup table with the server device IDs)
• Description
• Vendor
• IP number
o Field property: format "S_"@@\.@@\.@@\.@@ (may need to adjust for number)
o IP number data type: text
• Operating system (Windows 2008 Server)
• location

The number of devices with which you will populate your databases is below:

• No more than seventeen ( 17) desktop workstations (the number will depend on staffing you choose)
• Three+ (3+) (depending on staffing you choose) Laptops
• One (1) File server
• One (1) Applications server
• One (1) Web server
• One (1) Router
• Two (2) switches
• Four (4) printers (networked)

Use the following device-name prefixesand IP numbers in the desktop workstation, Laptop, Server and Network tables:

Device prefixes are as follows:
• Desktop: begin with DT (followed by the employee's last name)
• Laptops: begin with "LT" (followed by the employee's last name)
• Servers: begin with "S" (S1 or S2)
• Switches: use SW1 through SW10
• Printers:use P1 through P4
• Routers:use R1 through R2

IP numbers:

IP numbers for desktopsand Laptops are assigned through DHCP, so there is no need to enter them into the database. Servers, printers and routers have IP numbers within the following range. You can use any IP number in this range as a dedicated IP number:
25.13.55.16 - 25.13.55.255

b) QUERY

Design a query that links Employee, Desktop and Laptop tables, and returns a table listing data from the following fields. What you should have is a query that returns all a table containing all the employees in the database, what equipment they use, their location and name.
Query Name: Desktop/laptop
Query Items:

• Desktop workstation device or Laptop device
• Employee ID
• Last Name
• First Name
• Location

You will need to link the Desktop, Laptops and Employees tables for this query.

c) REPORTS

From the Desktop/laptop query you generated, create a report which lists employee ID, first and last name, location, and Device ID (desktop or laptop). The report should be arranged alphabetically by employee last name.

d) FORMS

From the Employee, Desktop, and Laptop tables, create three (3) forms in Columnar format reflecting all fields from in the Desktop, Laptop, and Employee tables. Enter your name in the Employee form, and then enter information for both a Desktop and Laptop computer.

Reference no: EM13896464

Questions Cloud

Discuss the pros and cons of open innovation : Discuss the pros and cons of open innovation. What major differences would you expect to find in the management approaches used for breakthrough innovation projects.

A company has year end cost of goods manufactured : 1.A company has year end cost of goods manufactured of $ 4,000, beginning finished goods inventory of $ 500, and ending finished goods inventory of $ 750. Its cost of goods sold is

Manufacturing statement for briton company : 1.Prepare the 2013 manufacturing statement for Briton Company using the following information.

What are the potential dis-advantages to standardization : Operations personnel tend to favor product component standardization while design and marketing personnel tend to resist it. Why is this true? What are the potential dis-advantages to standardization?

Create database that contains the inventory of organization : Create a database that contains the inventory of your organization's technology, as well a table including employee information. Build your database named IT_inventory, linking users to workstations, i.e., each computer (desktop or notebook) will b..

Nestl reports beginning raw materials inventory : 1.Nestl reports beginning raw materials inventory of 3,243 and ending raw materials inventory of 3,904 (both numbers in millions of Swiss francs).

Brave new world warns of the dangers : Brave New World warns of the dangers of giving the state control over new and powerful technologies. One illustration of this theme is the rigid control of reproduction through technological and medical intervention, including the surgical removal ..

Current assets for two different companies : Current assets for two different companies at calendar year end 2013 are listed here. One is a manufacturer, Salomon Skis Mfg., and the other, Sun Fresh Foods, is a grocery distribution company.

Assess the production process for the ram light assembly : Assess the production process for the Ram light assembly. How efficient is it? Develop a process map for this operation. Where are the largest opportunities to reduce waste and associated costs?

User Account

All Pages