CS157A_Final
pdf
keyboard_arrow_up
School
San Jose State University *
*We aren’t endorsed by this school
Course
157
Subject
Information Systems
Date
Feb 20, 2024
Type
Pages
8
Uploaded by tankhanhf7
Name: Houman Irani
Problem 1:
1- Normalization:
STAFF:
STAFF_ID PK
STAFF_NAME
DATE_JOINED
DATE_LEFT
EMAIL_ADDRESS
PHONE_EXTENSION
TRANSACTION:
TRANSACTION_ID PK
TRANSACTION_AMOUNT
PERIOD_ID FK
CURRENCY_NAME FK
STAFF_ID FK
ACCOUNT_ID FK
GENERAL_LEDGER_CODE FK
ACCOUNT :
ACCOUNT_ID PK
ACCOUNT_TYPE_NAME
DATE_ACCOUNT_OPEN
DATE_ACCOUNT_CLOSED
PERIOD:
PERIOD_ID PK
PERIOD_NAME
DATE_PERIOD_START
DATE_PERIOD_END
DEPARTMENT:
DEPARTMENT_CODE PK
DEPARTMENT_NAME
GENERAL_LEDGER:
GENERAL_LEDGER_CODE PK
GENERAL_LEDGER_CODE_DISCRIPTION
CURRENCY:
CURRENCY_CODE PK
CURRENCY_NAME
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
3- As soon as the TRANSACTIONS table was normalized, all new tables were generated, including
Currency, Account, Period, General Ledger, Department. Tables that once belonged to TRANSACTIONS
tables, now exist as their own separate tables with unique entities. In order to prevent duplicate data from
being stored in the TRANSACTIONS table, it was decided that the data should be kept in separate tables
in order to avoid data loss.
4- SQL Commands:
CREATE TABLE TRANSACTION (
TRANSACTION_ID INTEGER (PRIMARY KEY),
TRANSACTION_AMOUNT DECIMAL,
PERIOD_ID INTEGER,
CURRENCY_NAME VARCHAR(20),
STAFF_ID INTEGER,
ACCOUNT_ID INTEGER,
GENERAL_LEDGER_CODE VARCHAR(30),
PERIOD_ID FOREIGN KEY REFERENCES PERIOD (PERIOD_ID),
CURRENCY_NAME FOREIGN KEY REFERENCES CURRENCY (CURRENCY_NAME),
STAFF_ID FOREIGN KEY REFERENCES STAFF (STAFF_ID),
ACCOUNT_ID FOREIGN KEY REFERENCES ACCOUNTS (ACCOUNT_ID),
GENERAL_LEDGER_CODE FOREIGN KEY REFERENCES GENERAL_LEDGER
(GENERAL_LEDGER_CODE)
);
CREATE TABLE ACCOUNT (
ACCOUNT_ID INTEGER (PRIMARY KEY),
CUSTOMER_NAME VARCHAR(40),
ACCOUNT_TYPE_NAME VARCHAR,
DATE_ACCOUNT_OPENED DATE,
DATE_ACCOUNT_CLOSED DATE,
);
CREATE TABLE STAFF (
STAFF_ID INTEGER PRIMARY KEY,
STAFF_NAME VARCHAR(40),
DATE_JOINED DATE,
DATE_LEFT DATE,
EMAIL_ADDRESS VARCHAR(50),
PHONE_EXTENSION INTEGER
);
CREATE TABLE CURRENCY (
CURRENCY_CODE VARCHAR(20) PRIMARY KEY,
CURENCY_NAME VARCHAR(20) (PRIMARY_KEY)
);
CREATE TABLE PERIOD (
PERIOD_ID INTEGER (PRIMARY KEY),
PERIOD_NAME VARCHAR(20),
DATE_PERIOD_START DATE,
DATE_PERIOD_END DATE,
);
CREATE TABLE DEPARTMENT (
DEPARTMENT_CODE VARCHAR(20) (PRIMARY KEY),
DEPARTMENT_NAME VARCHAR(20)
);
CREATE TABLE GENERAL_LEDGER (
GENERAL_LEDGER_CODE VARCHAR(20) (PRIMARY_KEY),
GENERAL_LEDGER_CODE_DESCRIPTION VARCHAR(40)
);
5-
START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
INSERT INTO Transactions (TRANSACTION_AMOUNT, PERIOD_ID, CURRENCY_NAME,
STAFF_ID, ACCOUNT_ID, GENERAL_LEDGER_CODE)
VALUES (TRANSACTION_AMOUNT, PERIOD_ID, CURRENCY_NAME, STAFF_ID,
ACCOUNT_ID, GENERAL_LEDGER_CODE)
COMMIT;
Explanation:
The isolation level is read uncommitted because it is inserting data to the database so there is no chance of
dirty, non repeatable or phantom reads during the transaction.
6-
CREATE VIEW Transactions AS
SELECT *
FROM Transactions
LEFT JOIN Period
ON Transactions.PERIOD_ID = Period.PERIOD_ID AND Period.DATE_PERIOD_START >
10-01-2021 AND Period.DATE_PERIOD_END < 11-30-2021
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Problem 2:
I did denormalization for the transaction table by adding the account information which will increase
performance since when transaction info is retrieved from the database the account info must also be
retrieved and therefore only one table needs to be queried.
Problem 3:
use Bank
db.createCollection(‘staff’)
db.createCollection(‘transaction’)
Db.staff.insert ({
"_id": "Object(101)",
"staff_id": "1",
"staff_name": "Houman Irani",
"date_joined": "12-08-2021",
"date_left": "01-08-2022",
"email": "houman.irani@sjsu.edu",
"phone_extension": "310"
})
Db.transaction.insert ({
"_id": "Object(102)",
"staff_id": "Object(101)",
"transaction_id": 1,
"transaction_amount": 1000,
"account_id": "1",
"department_name": "banking",
"customer_name": "Adam",
"date_account_open": "08-13-1999",
"date_account_closed": "",
"department_code": "1",
"general_ledger_code": "1"
"account_type_name": "savings",
"gl_code_description": "description",
"period_name": "december",
"date_period_start": "12-01-2021",
"date_period_end": "12-31-2021",
"currency_code": "1",
"currency_name": "usd",
"period_id": "1"
})