Group 7 Victoria Elliott Zachary Xu Ken Cassidy Ryan Hunt Brent Trinh Ellis Geer

1. Group 7 Victoria Elliott Zachary Xu Ken Cassidy Ryan Hunt Brent Trinh Ellis Geer

2. Table of Contents • Introduction • Requirements Specification • Conceptual Schema • Relational Schema • Normalization • Tables & Sample Data • Sample Queries • Conclusion

3. Introduction • PNC Bank is a large banking company that consists of an eight state region with millions of customers. The company deals with personal banking as well as small business and corporate banking. Currently they have several different databases for customers, employees and locations. PNC wants to simplify its current database system into one centralized system. This will allow its employees to easily access and share all of the data.

4. Requirements Specification • At PNC each employee has a first and last name, 1-3 phone numbers, address, unique employee id, dept, team, mail stop. Each employee is assigned to only one branch and each branch has one-to-many employees. • Each customer has a unique account number, first and last name, address, 1-3 phone numbers, social security number, employer, email, date of birth. Customers make zero-to-many transactions but transactions only have one customer. • Each branch location has an address, branch number, hours, and phone number. Each branch has at most one manager. All managers are employees but not all employees are managers. Branches consist of one-to-many ATMs. ATMs can have zero to one branch. • Each ATM has a unique number, name, address, features, hours of operation, language, maintenance hours. • Each transaction has transaction number, post date, effective date, amount, balance, average balance, type. Transactions may be done at ATM or branch. ATMs and branches can have zero to many transactions.

5. ER DIAGRAM

6. Relational Schema • Step 0: • 0.1: Identify Entity Types: • Strong Entity Types: Employee, Branch, ATM, Transactions, Customers Weak Entity Types: None Superclass/Subclasses: None • 0.2: Identifying Relationship Types: • Many-to-Many: Branch:Transactions, ATM:Transactions • One-to-Many: Branch:ATM, Branch:Employee, Customer:Transactions • One-to-One: Employee:Branch • N-ary: None • 0.3: Identifying Special Attributes: • Composite Attributes: empName, empAddress, bAddress, aAddress, cName, cAddress • Multi-Valued Attributes: empPhoneNo, custPhoneNo • Derived Attributes: None

7. Relational Schema Con’t • Step 1: Strong Entity Types • Employee(employeeID, first, last, street, city, state, zipcode, department, team, mailstop, empEmail • Branch(branchNo, street, city, state, zipcode, branchHoursOfOperation, branchPhoneNo) • ATM(IDNumber,ATMName, street, city, state, zipcode, features, twentyFourHours, language, maintenanceHours) • Transactions(transactionNo, postdate, effectiveDate, amount, balance, avgBalance, type) • Customer(accountNo, first, last, street, city, state, zipcode, SSN, employer, custEmail, dateOfBirth)

8. Relational Schema Con’t • Step 2: Many-to-Many Relationships: • BranchEmployees(employeeID, branchNo) • FK employeeID references Employee(employeeID) • FK branchNo references Branch(branchNo) • ATMTransactions(IDNumber, transactionNo) • FK IDNumber references ATM(IDNumber) • FK transactionNo references Transactions(transactionNo)

9. Relational Schema Con’t • Step 3: One-to-Many Relationships: • Branch(branchNo, street, city, state, zipcode, branchHoursOfOperation, branchPhoneNo, IDNumber) • FK IDNumber references ATM(IDNumber) • Branch(branchNo, street, city, state, zipcode, branchHoursOfOperation, branchPhoneNo, employeeID ) • FK employeeID references Employee(employeeID)  • Branch(branchNo, street, city, state, zipcode, branchHoursOfOperation, branchPhoneNo, IDNumber, employeeID ) • FK IDNumber references ATM(IDNumber) • FK employeeID references Employee(employeeID)  • Customer(accountNo, first, last, street, city, state, zipcode, SSN, employer, custEmail, dateOfBirth, transactionNo) • FK transactionNo references Transactions(transactionNo)

10. Relational Schema Con’t • Step 4: One-to-One Relationships: • Employee (employeeID, first, last, street, city, state, zipcode, department, team, mailstop, empEmail, branchNo) • FK branchNo references Branch(branchNo) • Step 5: None • Step 6: None

11. Relational Schema Con’t • Step 7: Multi-Valued Atributes: • EmployeePhoneNo(empPhoneNo, employeeID) • FK employeeID references Employee(employeeID) • CustomerPhoneNo(custPhoneNo, accountNo) • FK accountNo references Customer(accountNo) • Step 8: Superclasses/Subclasses None

12. Final Relational Schema • Branch(branchNo, street, city, state, zipcode, branchHoursOfOperation, branchPhoneNo, IDNumber, employeeID ) • Customer(accountNo, last, first, street, city, state, zipcode, SSN, employer, custEmail, dateOfBirth, transactionNo) • Employee (employeeID, last, first, street, city, state, zipcode, department, team, mailstop, empEmail, branchNo) • ATM(IDNumber,ATMName, street, city, state, zipcode, features, twentyFourHours, language, maintenanceHours) • Transactions(transactionNo, postdate, effectiveDate, amount, balance, avgBalance, type) • BranchEmployees(employeeID, branchNo) • ATMTransactions(IDNumber, transactionNo) • EmployeePhoneNo(empPhoneNo, employeeID) • CustomerPhoneNo(custPhoneNo, accountNo)

13. Normalization Branch Customer Employee

14. Normalization Con’t ATM Transactions

15. Normalization Con’t BranchEmployees EmployeePhoneNo CustomerPhoneNo ATMTransactions

16. Tables • Branch • CREATE TABLE BRANCH (BRANCHNO varchar(100) NOT NULL,STREET varchar(20) NULL,CITY varchar(15) NULL,STATE char(2) NULL,ZIPCODE char(5) NULL,BRANCHHOURSOFOPERATION varchar(13) NULL,BRANCHPHONENO varchar(11) NULL,PRIMARY KEY (BRANCHNO) ); • INSERT INTO BRANCH VALUES('13 ATHERTON', '1408 N ATHERTON ST', 'STATE COLLEGE', 'PA', '16801', '9:00AM-4:30PM', '8142311702'); • INSERT INTO BRANCH VALUES('BELLEFONTE 1', '801 E. BISHOP ST', 'BELLEFONTE', 'PA', '16823', '8:30AM-5:00PM', '8143532345'); • INSERT INTO BRANCH VALUES('N 2ND ST', '2 N 2ND ST', 'HARRISBURG', 'PA', '17101', '8:00AM-4:00PM', '7172325626'); • INSERT INTO BRANCH VALUES('CAMP HILL', '2148 MARKET ST', 'CAMP HILL', 'PA', '17011', '8:00AM-4:00PM', '7177301070');

17. Tables Con’t • Employee • CREATE TABLE EMPLOYEE (EMPLOYEEID varchar(10) NOT NULL,FIRST varchar(20) NULL,LAST varchar(20) NULL,STREET varchar(20) NULL,CITY varchar(15) NULL,STATE char(2) NULL,ZIPCODE char(5) NULL,DEPARTMENT varchar(20) NULL,TEAM char(1) NULL,MAILSTOP varchar(10) NULL,EMPEMAIL varchar(15) NULL,BRANCHNO varchar(100) NOT NULL,PRIMARY KEY (EMPLOYEEID) ); • INSERT INTO EMPLOYEE VALUES('1479855321', 'HENRY', 'BI', '320 E BEAVER AVE', 'STATE COLLEGE', 'PA', '16801', 'MIS', '7', '56AA', 'HUB5@PNC.COM', '13 ATHERTON'); • INSERT INTO EMPLOYEE VALUES('1546579953', 'DENNIS', 'FETZER', '122 E COLLEGE AVE', 'STATE COLLEGE', 'PA', '16801', 'VICE PRESIDENT', '6', '45BE', 'DFETZER@PNC.COM', 'BELLEFONTE 1'); • INSERT INTO EMPLOYEE VALUES('1200347852', 'MICKEY', 'MOUSE', '1 WALT DISNEY AVE', 'ORLANDO', 'FL', '36455', 'TELLER', '5', '19KL', 'MMOUSE@PNC.COM', 'N 2ND ST'); • INSERT INTO EMPLOYEE VALUES('1562332078', 'DONALD', 'DUCK', '2 QUACK ST', 'BEVERLY HILLS', 'CA', '90210', 'MARKETING', '5', 'OG78', 'DDUCK@PNC.COM', 'N 2ND ST');

18. Sample Queries • List all of the ATM names, transaction numbers, and type of transactions for transaction numbers that contain the number 4. • SELECT ATMName, t.transactionNo, type • FROM ATM a, TRANSACTIONS t, ATMTransactions s • WHERE a.IDNumber = s.IDNumber • AND t.transactionNo = s.transactionNo • AND t.transactionNo LIKE '%4%';

19. Conclusion • Achievements • Created useful and simplified database • Effectively worked as a team • Gained meaningful experience for future projects • Problems • Had trouble arrange meeting times • Large group interaction • Meeting with an appropriate company representative