AIMS2710-Access Project Applied information managent systems You have recently b

Question

AIMS2710-Access Project

Applied information managent systems

You have recently been hired by Sharbaf and Associates, a small sized firm that offers consulting IT innovative services for the various industries. Your job is to create a database. The database should contain the following criteria information:

At least three tables (primary key for each table)(20 points).

At least 6 attributes (field) for the table 1, at least 5 attributes (field) for the table 2, and 3(10 points)

Each table should have at least 10 records (10 points).

Establish Entity Relationship Diagram (ERD) for the tables(1:1, 1:M, and M:M)(20 points)

Create forms for the tables.(10 points)

Create a query for the tables (establish your criteria for each table)( 20 points).

Create a report for the tables (10 points).

Explanation / Answer

PART 1 & 2-

Creating employee database, which contains three tables as follows-

DDL command for creating a database-

create database employee_record;

# using database

Use employee_record

DDL commands for creating all the three tables-

# Creating table employee with employee_id as primary key

Create table employee

       (

       employee_id         varchar(8),

       employee_name       varchar(20),

       contact_no          varchar(10),

       date_of_hire        date,

       salary              varchar(10),

       designation         varchar(15),

       manager_id          varchar(8),

       primary key (employee_id)

       );

# creating table department with dept_id as primary key, manager_id as

# foreign key to employee_id in employee table and location as foreign

# key to location id in location table

create table department

       (

       dept_id             varchar(8)

       dept_name           varchar(20),

       manager_id          varchar(8),

       location            varchar(8),

       work_nature         varchar(25),

       primary key (dept_id)

       foreign key (manager_id) references employee (employee_id)

       on delete cascade,

       foreign key (location) references location (location_id)

       on delete cascade

       );

# creating table location with location id as primary key

create table location

       (

       location_id                varchar(8)

       street_address             varchar(20),

       postal_code                varchar(5),

       city                       varchar(12),

       state                      varchar(10),

       country                    varchar(25),

       primary key (location_id)

       );

PART 3-

# inserting 10 records in each table

insert into employee values (‘emp-101’,'bob', '9999999999',’2017-10-10’, '2000', ‘clerk’, ‘emp-404’);

insert into employee values (‘emp-103’,'joy', '9988999999',’2016-10-10’, '3000', ‘supervisor’, ‘emp-405’);

insert into employee values (‘emp-404’,'taylor', '7799999999',’2015-01-10’, '2000', ‘manager-admin’, ‘emp-601’);

insert into employee values (‘emp-107’,'jack', '99999999977',’2016-10-05’, '2000', ‘clerk’, ‘emp-404’);

insert into employee values (‘emp-108’,'dab', '9999559999',’2014-03-10’, '1500', ‘clerk’, ‘emp-404’);

insert into employee values (‘emp-102’,'jinny', '9999999999',’2017-06-10’, '2000', ‘clerk’, ‘emp-404’);

insert into employee values (‘emp-106’,'jonny', '9988999999',’2016-04-10’, '3000', ‘supervisor’, ‘emp-405’);

insert into employee values (‘emp-408’,'tom', '7799999999',’2015-01-10’, '2000', ‘manager-ops’, ‘emp-601’);

insert into employee values (‘emp-110’,'peter', '99999999977',’2016-10-05’, '2000', ‘clerk’, ‘emp-404’);

insert into employee values (‘emp-202’,'jacob', '99995597699',’2014-03-30’, '1500', ‘clerk’, ‘emp-404’);

# inserting records in department table

insert into department values (‘adm-001’,'admin', 'emp-404',’loc-222’,‘administration’);

insert into department values (‘ops-001’,'operations', 'emp-408',’loc-223’,‘business operations’);

insert into department values (‘fin-001’,'finance', 'emp-608',’loc-225’,‘finance operations’);

# similary more records can be inserted in department table

# insert records to location table

insert into location values (‘loc-101’,’6-avenue’,’50662’,’fargo’,’North Dakota’,’America’);

insert into location values (‘loc-102’,’9th- Park road’,’50682’,’bismark’,’North Dakota’,’America’);

insert into location values (‘loc-102’,’9th- Park road’,’50682’,’valley’,’North Dakota’,’America’);

insert into location values (‘loc-222’,’Park road’,’50665’,’fargo’,’North Dakota’,’America’);

insert into location values (‘loc-222’,’Park road’,’50665’,’fargo’,’North Dakota’,’America’);

insert into location values (‘loc-223’,’K B Road’,’50662’,’fargo’,’North Dakota’,’America’);

insert into location values (‘loc-225’,’MG Road’,’50682’,’bismark’,’North Dakota’,’America’);

insert into location values (‘loc-111’,’J D Road’,’50682’,’valley’,’North Dakota’,’America’);

insert into location values (‘loc-109’,’BLD-21 Prestige’,’50665’,’fargo’,’North Dakota’,’America’);

insert into location values (‘loc-107’,’ BGL 07 Park road’,’50665’,’fargo’,’North Dakota’,’America’);

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.