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);
);
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 |
Help In Database: Next comes the data entry: Make up data for your database, or find...
I need help with the following SQL query for a company database (script given below). The name of the Department. The number of employees working in that department. The number of different projects controlled by this department. The name of the project controlled by this department that has the maximum number of employees of the company working on it. The number of the above project. The cumulative sum of the number of employees of the company working on the projects...
database and sql problme
THE QUESTIONS 3, 4,5 and 6 REFER TO THE RELATIONAL TABLES LISTED BELOW CREATE TABLE Department ( DECIMAL(5) VARCHAR(30) CHAR(5) DATE NOT NULL NOT NULL NOT NULL * Department number /*Department name * Department manager number */ /Manager start date DNumber DName Manager MSDate CONSTRAINT Department_PK PRIMARY KEY(DNumber) CONSTRAINT Department_CK UNIQUE(DName) CREATE TABLE DeptLocation DECIMAL(5) VARCHAR(50) NOT NULL NOT NULL DNumber * Department number */ * Department location */ Address CONSTRAINT DeptLocation_PK PRIMARY KEY(DNumber, Address) CONSTRAINT...
MICROSOFT SQL SERVER - Create the following views 1.List the employee assigned to the most projects 2.List the project with the most hours SCRIPT TO BUILD DATABASE create table department ( dept_ID int not null , dept_name char(50) NOT NULL, manager_ID int not null, manager_start_date date not null, constraint Dept_PK primary key (dept_ID), constraint D_Name_AK unique (dept_name) ); insert into department values(1,'abc',1,'2019-01-08'); insert into department values(2,'abc2',2,'2019-01-08'); insert into department values(3,'abc3',2,'2019-01-08'); insert into department values(4,'abc4',2,'2019-01-08'); /*project table done*/ create table project...
Using SQL and the following info Question 3: Find the people who do not have Viper Certification but are still assigned to Viper class ship Find the fname, lname, and ship_instance_id for all people who do not have Viper certification but are assigned to at least one instance of a Viper class ship (this includes all variants of Viper class ships). Return a row for every ship/person combination. Order your results by fname in ascending order. Use the BSG database...
Using SQL and the following info Question 3: Find the people who do not have Viper Certification but are still assigned to Viper class ship Find the fname, lname, and ship_instance_id for all people who do not have Viper certification but are assigned to at least one instance of a Viper class ship (this includes all variants of Viper class ships). Return a row for every ship/person combination. Order your results by fname in ascending order. Use the BSG database...
SQL Query Question: I have a database with the tables below, data has also been aded into these tables, I have 2 tasks: 1. Add a column to a relational table POSITIONS to store information about the total number of skills needed by each advertised position. A name of the column is up to you. Assume that no more than 9 skills are needed for each position. Next, use a single UPDATE statement to set the values in the new...
a database of employees that corresponds to the
employee-payroll hierarchy is provided (see employees.sql to create
the employees for a MySQL database). Write an application that
allows the user to:
Add employees to the employee table.
Add payroll information to the appropriate table for each new
employee. For example, for a salaried employee add the payroll
information to the salariedEmployees table
1 is the entity-relationship diagram for the
employees database
Figure 1: Table relationships in the employees database [1].
Add...
NEED HELP IN INSERT STATEMENTS FOR THE TABLES CAN YOU PLEASE ADD SOME INSERT STATEMENTS FOR A COMPANY SCHEMA SCRIPT. PLEASE ADN THANK YOU DROP TABLE dependent; DROP TABLE works_on; DROP TABLE project; DROP TABLE dept_locations; DROP TABLE department; DROP TABLE employee; CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary numeric(10,2), superssn char(9), dno numeric, primary key (ssn), foreign key (superssn) references employee(ssn) ); CREATE...
Define an SQL view JokesNum that gives the number of jokes each user posts on each day. Database: create table User( userid varchar(30) not null, password varchar(30), firstname varchar(30), lastname varchar(50), email varchar(50), gender char(1), age integer, banned boolean, primary key(userid), unique(email)); create table MyFriends( userid varchar(30), friendid varchar(30), primary key(userid,friendid), foreign key(userid) references User, foreign key(friendid) references User(userid)); Create table Jokes( Jokeid integer, title varchar(100), description varchar(255), authorid varchar(30) not null, primary key(jokeid), posttime Datetime, foreign key(authorid) references User(userid));...
Write an SQL query to return the users who posted the most number of jokes on 1/1/2019. Database: create table User( userid varchar(30) not null, password varchar(30), firstname varchar(30), lastname varchar(50), email varchar(50), gender char(1), age integer, banned boolean, primary key(userid), unique(email)); create table MyFriends( userid varchar(30), friendid varchar(30), primary key(userid,friendid), foreign key(userid) references User, foreign key(friendid) references User(userid)); Create table Jokes( Jokeid integer, title varchar(100), description varchar(255), authorid varchar(30) not null, primary key(jokeid), posttime Datetime, foreign key(authorid) references User(userid));...