Question

Help In Database:

Next comes the data entry:

Make up data for your database, or find real data.

You can enter the data with INSERT statements, but it is much easier to use PhpAdmin. It has a form where you can type the data directly. Alternatively, you can use PhpAdmin to import CSV data (comma-separated values), which you can export from Excel.

Include at least 3 rows in each of the entity relations, and as many rows as you need to represent the relationships.

When your database is complete, export the entire schema (the database with the data) using PhpAdmin. It should create one large .SQL file, which is just a text file with SQL code in it. Take a look at it. It must include all of the following:

CREATE TABLE statements for all the tables

Primary keys and foreign keys (usually part of create table)

INSERT statements for all the data

DataBase:

1. Employee Table

CREATE TABLE EMPLOYEE
( SocialSecurityNumber int NOT NULL,
salary Number(8,2),
FirstName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255),
CONSTRAINT employees_pk PRIMARY KEY (SocialSecurityNumber),
);

2. PROJECT TABLE

CREATE TABLE PROJECT
(
NAME varchar(255) NOT NULL,
NUMBERS INT NOT NULL,
LOCATIONS varchar(255),
CONSTRAINT project_pk PRIMARY KEY (NAME,NUMBERS),
);

3. DEPARTMENT

CREATE TABLE DEPARTMENT
( name char(255) NOT NULL,
numbers int NOT NULL,
locations varchar(255),
employees varchar(255),
PRIMARY KEY (name,numbers),
FOREIGN KEY (name,numbers) REFERENCES PROJECT(name,numbers);

4.DEPENDENTS

CREATE TABLE DEPENDENTS
( name char(255) NOT NULL,
numbers int NOT NULL,
sex varchar(1),
birthdate date,
relationship varchar(255),
PRIMARY KEY (name,numbers),
FOREIGN KEY (name,numbers) REFERENCES DEPARTMENT(name,numbers);
);

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using XAMPP server.Here Database and tables are created using PHPMyAdmin.

SQL Script for Database :

create Database Companydb;

Tables :

1.Table Name : Employee

SQL Script :

/*CREATE TABLE*/

CREATE TABLE EMPLOYEE
( SocialSecurityNumber int NOT NULL,
salary decimal(8,2),
FirstName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255),
CONSTRAINT employees_pk PRIMARY KEY (SocialSecurityNumber)
);

/*INSERT RECORDS INTO EMPLOYEE*/

Insert into employee values (999111111,30000,'Bock Douglas','#2 Mont Verd','Maxico');

Insert into employee values (999222222,25000,'Amin Hyder','S Seaside Apt. B','New York');

Insert into employee values (999333333,38000,'Joshi Dinesh','#10 Oak St.','Mumbai');

/*Retriving records from employee*/

select * from employee;

Screen in PHPMyAdmin:

*****************************

2.Table Name : Project

SQL Script :

CREATE TABLE PROJECT

(

NAME varchar(255) NOT NULL,

NUMBERS INT NOT NULL,

LOCATIONS varchar(255),

CONSTRAINT project_pk PRIMARY KEY (NAME,NUMBERS)

);

/*Insert records into Project*/

Insert into Project values ('Order Entry',1,'Pune');

Insert into Project values ('Payroll',2,'Mumbai');

Insert into Project values ('Receivables',3,'New York');

/*Retriving records from Project*/

select * from project;

Screen in PHPMyAdmin :

*******************************

3.Table Name : Department

SQL Script :

CREATE TABLE DEPARTMENT

( name char(255) NOT NULL,

numbers int NOT NULL,

locations varchar(255),

employees varchar(255),

PRIMARY KEY (name,numbers),

FOREIGN KEY (name,numbers) REFERENCES PROJECT(name,numbers));

/*Insert records*/

Insert into Department values ('Production',1,'Maxico',999111111);

Insert into Department values ('Headquarters',2,'Mumbai',999222222);

Insert into Department values ('Research',3,'NY',999333333);

/*Selecting records*/

select * from departments;

Screen in PHPMyAdmin :

****************************************

4.Table Name : Dependents

SQL Script :

CREATE TABLE DEPENDENTS
( name char(255) NOT NULL,
numbers int NOT NULL,
sex varchar(1),
birthdate date,
relationship varchar(255),
PRIMARY KEY (name,numbers),
FOREIGN KEY (name,numbers) REFERENCES DEPARTMENT(name,numbers)
);

/*Insert records*/

INSERT INTO dependents VALUES ('Jo Ellen',999222222, 'F', '1996-04-05', 'DAUGHTER');
INSERT INTO dependents VALUES ('Andrew',999111111, 'M', '1998-10-25', 'SON');
INSERT INTO dependents VALUES ('Susan',999333333, 'F', '1975-05-03', 'SPOUSE');
INSERT INTO dependents VALUES ('Allen',,999444444, 'M', '1968-02-29', 'SPOUSE');
INSERT INTO dependents VALUES ('Jeffery',999555555 ,'M', '1978-01-01', 'SON');

/*Selecting records*/

select * from dependents;

Screen in PHPMyAdmin :

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.

30000.00