DDL ATM PROJECT
1. CREATE TABLE operator (
employeeid INTEGER,
name VARCHAR(256),
loginid INTEGER,
password INTEGER
CONSTRAINT operator_pk PRIMARY KEY (employeeid)
);
insert into operator values (1, 'Joe', 987654321, 1985)
insert into operator values (2, 'Jen', 908754321, 2000)
insert into operator values (3, 'Julia', 908785421, 5683)
insert into operator values (4, 'Max', 098564321, 8734)
2. CREATE TABLE atm (
employeeid INTEGER,
atmid INTEGER,
location VARCHAR(256),
amount MONEY,
CONSTRAINT atm_pk PRIMARY KEY (employeeid,atmid),
CONSTRAINT atm_employeeid_fk FOREIGN KEY(employeeid) REFERENCES operator(employeeid),
);
insert into atm values (1, 1, 'virginia avenue', 5000)
insert into atm values (1, 2, 'columbia plaza', 8000)
insert into atm values (2, 1, 'north hampton', 4500)
insert into atm values (2, 2, 'south park', 6500)
3. CREATE TABLE customerprofile (
customerid INTEGER,
name VARCHAR(256),
socialsecuritynumber INTEGER,
cardnumber INTEGER,
loginid INTEGER,
password INTEGER,
CONSTRAINT customerprofile_customerid_pk PRIMARY KEY(customerid),
CONSTRAINT customerprofile_fk FOREIGN KEY(cardnumber) REFERENCES cardinformation(cardnumber)
);
insert into customerprofile values (1,'naynish', 123456789, 739435304, 8976, 7890)
insert into customerprofile values (2,'amol', 783456099, 598768749, 4957, 3453)
4. CREATE TABLE cardinformation (
cardnumber INTEGER,
cvvnumber INTEGER,
cardtype VARCHAR (256),
dateofissue DATETIME,
datofexpiry DATETIME,
CONSTRAINT cardinformation_pk PRIMARY KEY (cardnumber)
);
insert into cardinformation values (739435304, 899, 'gold', '2011-02-05 12:02:43', '2011-02-05 12:02:43')
insert into cardinformation values (739434566, 899, 'silver', '2011-02-05 12:02:43', '2011-02-05 12:02:43')
insert into cardinformation values (598768749, 456, 'platinum', '2011-02-05 12:02:43', '2011-02-05 12:02:43')
5. CREATE TABLE savingsaccount (
customerid INTEGER,
transactiontype VARCHAR(256),
transactionby VARCHAR(256),
amount INTEGER,
time DATETIME,
location VARCHAR(256),
balance INTEGER,
CONSTRAINT savingaccount_fk FOREIGN KEY(customerid) REFERENCES customerprofile(customerid)
);
insert into savingsaccount values (1, 'D', 'self', 5000, '2011-02-05 12:02:43', 'columbia plaza', 5000)
insert into savingsaccount values (1, 'W', 'self', 1000, '2011-02-05 12:02:43', 'columbia plaza', 4000)
insert into savingsaccount values (1, 'D', '2', 3000, '2011-02-05 12:02:43', 'columbia plaza', 7000)
6. CREATE TABLE checkingaccount (
customerid INTEGER,
transactiontype VARCHAR(256),
transactionby VARCHAR(256),
amount INTEGER,
time DATETIME,
location VARCHAR(256),
balance INTEGER,
CONSTRAINT checkingaccount_fk FOREIGN KEY(customerid) REFERENCES customerprofile(customerid)
);
insert into checkingaccount values (1, 'D', 'self', 5000, '2011-02-05 12:02:43', 'virginia', 5000)
insert into checkingaccount values (2, 'W', 'self', 1000, '2011-02-05 12:02:43', 'columbia plaza', 4000)
insert into checkingaccount values (1, 'D', '2', 3000, '2011-02-05 12:02:43', 'columbia plaza', 8000)
No comments:
Post a Comment