Database Assignment Help, Online Tutor, Help with Database Homework, Problem Solution

Database Assignment Help | Project | Homework Problem

DatabaseHomeworkHelp is a dedicated website for database homework help.We incorporate live database experts to provide help with database homework assignment.Services we deliver are database management project help,online tutoring, database management homework help and solutions to the questions related to SQL.Getting solution for complex database problems is not difficult now with our assistance. RDBMS, DBMS, SQL Query Problems, Database Design, Commands and building database architecture are few areas in which you can get homework help from our platform.

Database is the building block for any website. It is also a foundation stone in order to understand the advance topics like data warehousing, data mining and today’s buzz ” Big Data”. Students pursuing technical courses across globes need to understand it in depth. They often struggle in their Database Assignment and look for Database homework help or database project help.

DatabaseHomeworkHelp is a single point solution for all your database problem or database queries. Students who are looking for advance and unique Database homework help or help with Database scripts or Help with database program can visit us for the same. Topics like Normalization, ACID properties, SQL and PL-SQL scripts needs better understanding thus students often desire high quality database assignment Help or database report write-up help or database analysis help. Experts hre are real time programmers with more than 10 years of Database tutoring experience. They provide in-depth explanation while providing online Database tutoring help. They are available 24X7 for your assistance and help to make you understand and solve any problem related to database in a quick span of time.

Database Homework Sample Assignment

Question: – To show the relationship and to create and update normalized relations of the data from the below scenario,
“You have been commissioned by Barry from the Fantastic Fireworks Company (FFC). He has asked you to design a database to assist FFC with managing their booking, customer, staff, product and supplier information. FFC stores information on their customer (individuals or Organizations). For individual customers they would like to store the contact name, address details including their location, postal and delivery address details), email address and phone numbers (mobile,home, work). For their organisational customers they would like to store the organisation name, organisation type (e.g. corporation, government, club), a contact name, address details (including their location, postal and delivery address details), email address, website URL, and phone numbers (mobile, work). The location address and work phone number is always recorded for a customer. Postal and delivery addresses and mobile and home phone numbers are sometimes not available so these are optionally recorded.
Similarly FFC has a number of different suppliers who provide them with the products that enables them to conduct displays. For each supplier they would like to store their business name, contact name, address details (including their location, postal and delivery address details), and phone numbers (mobile, work).”Please assume some of the attributes information for the entity.

Question 2:- After generating the above ER Diagram, then use that database to generate the reports detailed below,

Names of people should be printed as GivenNameFamilyName (e.g. John Smith) in a column labelled NAME
Addresses should be printed as Street, Suburb State Postcode (e.g. 123 Anzac Pde, Maroubra NSW 2038) in a column labelled ADDRESS
A list of the names and phone numbers (including description of phone type) of all customers in
alphabetical order by contact name.
A list of all government customers and their contact details (contact name, email and all phone
details).
List all supplier products where the product description begins with an E (either upper case or
lower case).
A list of all customers (id and name) together with their display(s) including date, time and type of
the display and the percentage markup less discount percentage for the display.
A list of the contact details (names, postal address and phone number) of all of the suppliers who
supplied products for more than one display.
List each display (displayed is sufficient) and all of the details of staff who have worked on the
display, including any certifications.
Display a list of products that have had a unit charge update in the last 12 months.
Provide a list of all displays, including customer name, display id, date, time, location for which
actual quantities used have been greater than allocated quantities. Include details of the product to
which this applies as well as the cost difference.
Display the itemised actual product cost of all items for displays in January 2013 (based on DisplayDate).
Increase by 5% the insurance cost for all insurance categories.

Solution:-
Below are the sql queries run to generate the above report,
Query1:-
CREATE DATABASE IF NOT EXISTS myDatabase;

USE myDatabase;
Query2:-
CREATE TABLE AddressType
(
AddressType INT NOT NULL,
Description VARCHAR(30),
PRIMARY KEY(AddressType)
);
Query3:-
CREATE TABLE PhoneType
(
PhoneType INT NOT NULL,
Description VARCHAR(30),
PRIMARY KEY(PhoneType)
);
Query4:-
CREATE TABLE DisplayType
(
DisplayType INT NOT NULL,
Description VARCHAR(30),
PRIMARY KEY(DisplayType)
);
Query5:-
CREATE TABLE OrgType
(
OrgType INT NOT NULL,
Description VARCHAR(30),
PRIMARY KEY(OrgType)
);
Query6:-
CREATE TABLE Product
(
ProductID INT NOT NULL,
Description VARCHAR(30),
PRIMARY KEY(ProductID)
);

Query7:-
CREATE TABLE StaffType
(
StaffType INT NOT NULL,
Description VARCHAR(30),
PRIMARY KEY (StaffType)
);

Query8:-
CREATE TABLE CertificateType
(
CertificateType INT NOT NULL,
Description VARCHAR(30),
PRIMARY KEY (CertificateType)
);

Query9:-
CREATE TABLE ExtBusiness
(
ExtBusinessID INT NOT NULL,
ContactName VARCHAR(30),
PRIMARY KEY(ExtBusinessID)
);
Query10:-
CREATE TABLE Insurance
(
InsuranceCategory INT NOT NULL,
Description VARCHAR(30),
Cost DECIMAL(13,2),
PRIMARY KEY(InsuranceCategory)
);

Query11:-
CREATE TABLE Customer
(
CustomerID VARCHAR(5) NOT NULL,
EmailAddress VARCHAR(30),
ExtBusinessID INT NOT NULL,
OrganisationName VARCHAR(30),
Website VARCHAR(100),
OrgType INT,
PRIMARY KEY(CustomerID),
FOREIGN KEY (OrgType) REFERENCES OrgType(OrgType),
FOREIGN KEY (ExtBusinessID) REFERENCES ExtBusiness(ExtBusinessID)
);

Query12:-
CREATE TABLE Supplier
(
SupplierID VARCHAR(5) NOT NULL,
SupplierName VARCHAR(30),
ExtBusinessID INT NOT NULL,
PRIMARY KEY(SupplierID),
FOREIGN KEY (ExtBusinessID) REFERENCES ExtBusiness(ExtBusinessID)
);
–
CREATE TABLE Staff
(
StaffID INT NOT NULL,
StaffName VARCHAR(60),
Query13:StaffType INT NOT NULL,
TFN VARCHAR(9),
Line1 VARCHAR(30),
City VARCHAR(30),
State VARCHAR(4),
PostCode VARCHAR(4),
Phone VARCHAR(15),
SupervisorID INT,
PRIMARY KEY(StaffID),
FOREIGN KEY (StaffType) REFERENCES StaffType(StaffType),
FOREIGN KEY (SupervisorID) REFERENCES Staff(StaffID)
);

Query14:
CREATE TABLE Display
(
DisplayID INT NOT NULL,
DisplayDate DATE,
DisplayTime TIME,
NumberAttendees INT,
Location VARCHAR(50),
DisplayType INT NOT NULL,
CustomerID VARCHAR(5) NOT NULL,
InsuranceCategory INT NOT NULL,
DisplayManager INT NOT NULL,
PRIMARY KEY(DisplayID),
FOREIGN KEY (DisplayType ) REFERENCES DisplayType(DisplayType),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
FOREIGN KEY (InsuranceCategory) REFERENCES Insurance(InsuranceCategory),
FOREIGN KEY (DisplayManager) REFERENCES Staff(StaffID)
);

Query15:
CREATE TABLE SupplierProduct
(
SupplierID VARCHAR(5) NOT NULL,
ProductID INT NOT NULL,
PRIMARY KEY(SupplierID, ProductID),
FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID),
FOREIGN KEY (ProductID) REFERENCES Product(ProductID)

);
Query16:
CREATE TABLE DisplaySupplierProduct
(
DisplayID INT NOT NULL,
SupplierID VARCHAR(5) NOT NULL,
ProductID INT NOT NULL,
PRIMARY KEY(DisplayID,SupplierID,ProductID),
FOREIGN KEY (DisplayID) REFERENCES Display(DisplayID),
FOREIGN KEY (SupplierID,ProductID) REFERENCES SupplierProduct(SupplierID,ProductID)

);

Query17:
CREATE TABLE staffCertifications
(
StaffCertificateID INT NOT NULL,
StaffID INT NOT NULL,
CertificateNumber VARCHAR(20),
Description VARCHAR(30),
CertificateType INT NOT NULL,
AwardedBy VARCHAR(30),
DateAwarded DATE,
ExpiryDate DATE,
PRIMARY KEY(StaffCertificateID),
FOREIGN KEY (StaffID) REFERENCES Staff(StaffID),
FOREIGN KEY (CertificateType) REFERENCES CertificateType(CertificateType)
);

Query18:

CREATE TABLE Phone
(
ExtBusinessID INT NOT NULL,
PhoneType INT NOT NULL,
PhoneNumber VARCHAR(15),
PRIMARY KEY (ExtBusinessID, PhoneType),
FOREIGN KEY (ExtBusinessID) REFERENCES ExtBusiness(ExtBusinessID),
FOREIGN KEY (PhoneType) REFERENCES PhoneType(PhoneType)

);
Query19:
CREATE TABLE Address
(
ExtBusinessID INT NOT NULL,
AddressType INT NOT NULL,
Line1 VARCHAR(40),
City VARCHAR(40),
Postcode VARCHAR(4),
PRIMARY KEY (ExtBusinessID, AddressType),
FOREIGN KEY (ExtBusinessID) REFERENCES ExtBusiness(ExtBusinessID),
FOREIGN KEY (AddressType) REFERENCES AddressType(AddressType)
);

Query20:
CREATE TABLE DisplayStaff
(
DisplayID INT NOT NULL,
StaffID INT NOT NULL,
PRIMARY KEY (DisplayID, StaffID),
FOREIGN KEY (DisplayID) REFERENCES Display(DisplayID),
FOREIGN KEY (StaffID) REFERENCES Staff(StaffID)
);

Question:- To create the database, the tables and insert sufficient data to test the required reports. You will be given two files to commence with (reflecting the database at the end of Assignment1)and these must be updated to include the changes detailed above,

Create a text file called YourStudentId‐Create.sql (format xxxxxxx‐Create.sql) for example 2225991‐
Create.sql that will:
a. Create a database calledABCYourStudentID (egABC30011111)
b. Creates all of the required tables including primary keys, foreign keys and their
relationships.
Create a text file called YourStudentId‐Insert.sql (format xxxxxxx‐Insert.sql) for example 2225991‐Insert.sql that will:
a. Insert sufficient data into each table you have created to test the queries. Sample data
has been provided, however, you will need to include further data to test all of the
required reports.
b. You are required to include your name as one of the customers; you can provide fake
details for the address but you are required to include your full name and use your student
number as the phone number for this customer.
Create a text file called YourStudentId‐Queries.sql (format xxxxxxx‐Queries.sql ‐ for example 25991‐Queries.sql) that contains all of the queries to display the required reports.
Solution:-
Qeuery:-

CREATE DATABASE IF NOT EXISTS myDatabase;

USE myDatabase;

CREATE TABLE AddressType
(
AddressType INT NOT NULL,
Description VARCHAR(30),
PRIMARY KEY(AddressType)
);

CREATE TABLE PhoneType
(
PhoneType INT NOT NULL,
Description VARCHAR(30),
PRIMARY KEY(PhoneType)
);

CREATE TABLE DisplayType
(
DisplayType INT NOT NULL,
Description VARCHAR(30),
PRIMARY KEY(DisplayType)
);

CREATE TABLE OrgType
(
OrgType INT NOT NULL,
Description VARCHAR(30),
PRIMARY KEY(OrgType)
);

CREATE TABLE Product
(
ProductID INT NOT NULL,
Description VARCHAR(30),
PRIMARY KEY(ProductID)
);

CREATE TABLE StaffType
(
StaffType INT NOT NULL,
Description VARCHAR(30),
PRIMARY KEY (StaffType)
);

CREATE TABLE CertificateType
(
CertificateType INT NOT NULL,
Description VARCHAR(30),
PRIMARY KEY (CertificateType)
);

CREATE TABLE ExtBusiness
(
ExtBusinessID INT NOT NULL,
ContactName VARCHAR(30),
PRIMARY KEY(ExtBusinessID)
);

CREATE TABLE Insurance
(
InsuranceCategory INT NOT NULL,
Description VARCHAR(30),
Cost DECIMAL(13,2),
PRIMARY KEY(InsuranceCategory)
);

CREATE TABLE Customer
(
CustomerID VARCHAR(5) NOT NULL,
EmailAddress VARCHAR(30),
ExtBusinessID INT NOT NULL,
OrganisationName VARCHAR(30),
Website VARCHAR(100),
OrgType INT,
PRIMARY KEY(CustomerID),
FOREIGN KEY (OrgType) REFERENCES OrgType(OrgType),
FOREIGN KEY (ExtBusinessID) REFERENCES ExtBusiness(ExtBusinessID)
);

CREATE TABLE Supplier
(
SupplierID VARCHAR(5) NOT NULL,
SupplierName VARCHAR(30),
ExtBusinessID INT NOT NULL,
PRIMARY KEY(SupplierID),
FOREIGN KEY (ExtBusinessID) REFERENCES ExtBusiness(ExtBusinessID)
);

CREATE TABLE Staff
(
StaffID INT NOT NULL,
StaffName VARCHAR(60),
StaffType INT NOT NULL,
TFN VARCHAR(9),
Line1 VARCHAR(30),
City VARCHAR(30),
State VARCHAR(4),
PostCode VARCHAR(4),
Phone VARCHAR(15),
SupervisorID INT,
PRIMARY KEY(StaffID),
FOREIGN KEY (StaffType) REFERENCES StaffType(StaffType),
FOREIGN KEY (SupervisorID) REFERENCES Staff(StaffID)
);

CREATE TABLE Display
(
DisplayID INT NOT NULL,
DisplayDate DATE,
DisplayTime TIME,
NumberAttendees INT,
Location VARCHAR(50),
DisplayType INT NOT NULL,
CustomerID VARCHAR(5) NOT NULL,
InsuranceCategory INT NOT NULL,
DisplayManager INT NOT NULL,
PRIMARY KEY(DisplayID),
FOREIGN KEY (DisplayType ) REFERENCES DisplayType(DisplayType),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
FOREIGN KEY (InsuranceCategory) REFERENCES Insurance(InsuranceCategory),
FOREIGN KEY (DisplayManager) REFERENCES Staff(StaffID)
);

CREATE TABLE SupplierProduct
(
SupplierID VARCHAR(5) NOT NULL,
ProductID INT NOT NULL,
PRIMARY KEY(SupplierID, ProductID),
FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID),
FOREIGN KEY (ProductID) REFERENCES Product(ProductID)

);

CREATE TABLE DisplaySupplierProduct
(
DisplayID INT NOT NULL,
SupplierID VARCHAR(5) NOT NULL,
ProductID INT NOT NULL,
PRIMARY KEY(DisplayID,SupplierID,ProductID),
FOREIGN KEY (DisplayID) REFERENCES Display(DisplayID),
FOREIGN KEY (SupplierID,ProductID) REFERENCES SupplierProduct(SupplierID,ProductID)

);

CREATE TABLE staffCertifications
(
StaffCertificateID INT NOT NULL,
StaffID INT NOT NULL,
CertificateNumber VARCHAR(20),
Description VARCHAR(30),
CertificateType INT NOT NULL,
AwardedBy VARCHAR(30),
DateAwarded DATE,
ExpiryDate DATE,
PRIMARY KEY(StaffCertificateID),
FOREIGN KEY (StaffID) REFERENCES Staff(StaffID),
FOREIGN KEY (CertificateType) REFERENCES CertificateType(CertificateType)
);

);

CREATE TABLE Address
(
ExtBusinessID INT NOT NULL,
AddressType INT NOT NULL,
Line1 VARCHAR(40),
City VARCHAR(40),
Postcode VARCHAR(4),
PRIMARY KEY (ExtBusinessID, AddressType),
FOREIGN KEY (ExtBusinessID) REFERENCES ExtBusiness(ExtBusinessID),
FOREIGN KEY (AddressType) REFERENCES AddressType(AddressType)
);

CREATE TABLE DisplayStaff
(
DisplayID INT NOT NULL,
StaffID INT NOT NULL,
PRIMARY KEY (DisplayID, StaffID),
FOREIGN KEY (DisplayID) REFERENCES Display(DisplayID),
FOREIGN KEY (StaffID) REFERENCES Staff(StaffID)
);

The multitude of database softwares and their different syntaxes make life complex and hence Database Homework Help is a service which can act like a life saver. Our dedicated experts are available 24/7 to provide online database assignment help. All you need to do is submit your requirements for database homework help and we will revert with a price quote in a few minutes.

Help with Relational databases or RDBMS Assignment Help are required while dealing with structured data and still form the foundations of enterprise wide Data Warehousing and Business Intelligence and Data Analytics applications. With advancement in technology, Big Data applications are in vogue and to deal with unstructed data, a new generation of databases has evolved.

Students from colleges and universities need guidance for database assignments and database homework. We, at Databasehomeworkhelp aim to cater to entire range needs of students such as Database Assignment Help,Online Tutoring and Coursework Help. We provide quality and accurate solutions to students across the globe for various database such as SQL, Oracle, PL-SQL, MYSQL, Acess, DBMS etc. We provide the help ranging from small homework in database to the complex database projects. So, if you are stuck in any of your Database Homework Assignment, Databasehomeworkhelp is there to rescue you. Our experienced online tutors are available 24*7 to provide Help with Database Assignment.You can also book a session with our experts who will facilitate your learning in simplistic step-by-step manner via Database Online tutoring.

So, send your Database Assignment, Homework or Project along with the deadlines to us and we will revert with accurate and quality solutions well before your deadline. With our unique dedicated approach, distinguished experts we assure you the highest grades in all of your submissions with us.

Normalization

Star Schema

UML Diagram

Use case Diagram

Relational Algebra

Database Assignment Help | Project | Homework Problem

Database Homework Sample Assignment