Consider the following E-R diagram, write SQL statements to create the correspon

Question

Consider the following E-R diagram, write SQL statements to create the corresponding relations and capture as many of the constraints as possible. If cannot capture some constraints, explain why. Make sure that the data type and size for each attribute are appropriate. For example, a numerical data type is better choice than a string data type for the attribute weight.   In addition, write SQL statements to load data into each relation. You are required to come up with at least 10 records for each table.  

HOME Type o hone ddre Branches Held alance Accounts Loherkings Savings interestRate a Homework 3 SpnnQ 2017 Word ployees Work Manages akes. M Loans Paymer addres Custorre Trunsucliu time here length. Christopher Mclean 9.22 PM 2/12/2011 19

Explanation / Answer

Branches:

CREATE TABLE Branches
(
    Branchid integer NOT NULL,
    Name varchar(20),
    Address varchar(100),
    City varchar(64),
   State varchar(32),
   Telephone varchar(32),
   Budget numeric(10,2) NOT NULL DEFAULT 0,
    CONSTRAINT PKBranches PRIMARY KEY(Branchid)
);

Employees:

CREATE TABLE Employees
(
    Empid integer NOT NULL,
    fName varchar(20),
   lName varchar(20),
   Ssn // text is not clear in given image
    Address varchar(100),
    Phone varchar(32),
   StartDate date NOT NULL,
   Length // text is not clear in given image
   Salary integer NOT NULL,
    PRIMARY KEY(Empid)
);

SeniorEmployees:
CREATE TABLE SeniorEmployees
(
   Empid integer NOT NULL,
    Branchid integer NOT NULL,
    PRIMARY KEY(Empid)
    FOREIGN KEY (Empid) REFERENCES Employees(Empid)
   FOREIGN KEY (Branchid) REFERENCES Branches(Branchid)
);

Customers:
CREATE TABLE Customers
(
    CustId integer NOT NULL,
    fName varchar(20),
   mName varchar(20),
   lName varchar(20),
    Phone varchar(32),
   Address varchar(100),
   Ssn // text is not clear in given image
   Password varchar(50),
   DateOfBirth date,
   PRIMARY KEY(CustId)
);

loans:
CREATE TABLE Loans
(
    LoanId integer NOT NULL,
    Amount numeric(10,2) NOT NULL DEFAULT 0,
   Interest numeric(10,2) NOT NULL DEFAULT 0,
   Length integer NOT NULL,
   PRIMARY KEY(LoanId)
);

Loanpayments:
CREATE TABLE Loanpayments
(
   Number integer NOT NULL,
   LoanId integer NOT NULL,
   Date date NOT NULL,
   Amount numeric(10,2) NOT NULL DEFAULT 0,
   FOREIGN KEY (LoanId) REFERENCES Loans(LoanId)
);

Accounts:
CREATE TABLE Accounts
(
    AccId integer NOT NULL,
    Balance numeric(10,2) NOT NULL DEFAULT 0,
   DateLastAccess date,
   PRIMARY KEY(AccId)
);

checkings:
CREATE TABLE checkings
(
   Checkid integer NOT NULL,
    Draft varchar(20),
   PRIMARY KEY(Checkid)
);

Savings:
CREATE TABLE Savings
(
   Saveid integer NOT NULL,
    InterestRate numeric(10,2) NOT NULL DEFAULT 0,
   PRIMARY KEY(Saveid)
);

Worksin:
CREATE TABLE Worksin
(
    Workid integer NOT NULL,
    Empid integer NOT NULL,
   Branchid integer NOT NULL,
   PRIMARY KEY(Workid)
   FOREIGN KEY (Empid) REFERENCES Employees(Empid)
   FOREIGN KEY (Branchid) REFERENCES Branches(Branchid)
);

Transaction:
CREATE TABLE Transaction
(
    TransId integer NOT NULL,
    AccId integer NOT NULL,
   CustId integer NOT NULL,
   Date date,
   Time time,
   Amount numeric(10,2) NOT NULL DEFAULT 0,
   PRIMARY KEY(TransId)
   FOREIGN KEY (AccId) REFERENCES Accounts(AccId)
   FOREIGN KEY (CustId) REFERENCES Customers(CustId)
);

Held:
CREATE TABLE Held
(
    Heldid integer NOT NULL,
    AccId integer NOT NULL,
   CustId integer NOT NULL,
   Branchid integer NOT NULL,
   PRIMARY KEY(Heldid)
   FOREIGN KEY (AccId) REFERENCES Accounts(AccId)
   FOREIGN KEY (CustId) REFERENCES Customers(CustId)
   FOREIGN KEY (Branchid) REFERENCES Branches(Branchid)
);

Takes:
CREATE TABLE Takes
(
    Takesid integer NOT NULL,
    Loanid integer NOT NULL,
   CustId integer NOT NULL,
   Branchid integer NOT NULL,
   PRIMARY KEY(Takesid)
   FOREIGN KEY (Loanid) REFERENCES Loans(Loanid)
   FOREIGN KEY (CustId) REFERENCES Customers(CustId)
   FOREIGN KEY (Branchid) REFERENCES Branches(Branchid)
);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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