Individual: Database Creation
InsaneUnk
database_files.zipDatabase files/Database_queries.sql
create table Person( PersonID int not null primary key, firstName varchar(50), lastName varchar(50), emailID varchar(100), contactNo varchar(20), dateOfBirth date ) create table Address( PersonID int foreign key references Person(PersonID), city varchar(50), country varchar(50), fullAddress varchar(150) ) create table Client( ClientID int not null primary key, PersonID int foreign key references Person(PersonID), clientType varchar(50), startDate datetime ) create table Employee( EmployeeID int not null primary key, PersonID int foreign key references Person(PersonID), designation varchar(50), joinDate datetime, salary money ) create table Supplier( SupplierID int not null primary key, PersonID int foreign key references Person(PersonID), supplierType varchar(50) ) create table Item( ItemID int not null primary key, SupplierID int foreign key references Supplier(SupplierID), itemName varchar(50), quantity int, buyingPrice money, sellingPrice money ) create table Invoice( InvoiceID int not null primary key, SupplierID int foreign key references Supplier(SupplierID), ClientID int foreign key references Client(ClientID), invoiceDate datetime, invoiceType varchar(50), totalBill money ) create table InvoiceItem( InvoiceID int foreign key references Invoice(InvoiceID), ItemID int foreign key references Item(ItemID), unitPrice money, amount int )
__MACOSX/Database files/._Database_queries.sql
Database files/Diagram.png
__MACOSX/Database files/._Diagram.png
Database files/Entries.txt
INSERT into Persons VALUES (1, 'sikandar', 'tariq', '[email protected]', '03012920011', '01-01-02' ) INSERT into Persons VALUES (2, 'hammad', 'fahim', '[email protected]', '03029191011', '01-01-05' ) INSERT into Persons VALUES (3, 'zohaib', 'rasheed', '[email protected]', '03012122111', '01-01-02' ) INSERT into Persons VALUES (4, 'inam', 'baig', '[email protected]', '03009559778', '01-01-06' ) INSERT into Persons VALUES (5, 'haider', 'kha', '[email protected]', '03212929291', '01-01-02' ) INSERT into Persons VALUES (6, 'jo', 'm', '[email protected]', '01202100202', '01-01-02' ) INSERT into Persons VALUES (7, 'Ali', 'mirza', '[email protected]', '03001200210', '01-01-02' ) INSERT into Persons VALUES (8, 'muhi', 'di', '[email protected]', '03001202021', '01-01-02' ) INSERT into Persons VALUES (9, 'ali', 'baig', '[email protected]', '03003003020', '01-01-02' ) INSERT into Persons VALUES (10, 'saad', 'ali', '[email protected]', '03002102021', '01-01-02' ) INSERT into Address VALUES (1, 'faisalabad', 'pakista', 'p.219') INSERT into Address VALUES (2, 'lahore', 'pakista', 'p.299') INSERT into Address VALUES (3, 'lahore', 'pakista', 'p.881') INSERT into Address VALUES (4, 'lahore', 'pakista', 'p.399') INSERT into Address VALUES (5, 'faisalabad', 'pakista', 'p.299') INSERT into Address VALUES (6, 'faisalabad', 'pakista', 'p.300') INSERT into Address VALUES (7, 'faisalabad', 'pakista', 'p.121') INSERT into Address VALUES (8, 'faislaabad', 'paksita', 'p.299') INSERT into Address VALUES (9, 'faisalabad', 'lahore', 'p.300') INSERT into Address VALUES (10, 'lahore', 'lahore', 'p.232') INSERT into Client VALUES (1, 1, 'glod', '01-01-02' ) INSERT into Client VALUES (2, 2, 'glod', '01-01-02' ) INSERT into Client VALUES (3, 3, 'silver', '01-01-02' ) INSERT into Client VALUES (4, 4, 'gold', '01-01-02' ) INSERT into Client VALUES (5, 5, 'silver', '01-01-02' ) INSERT into Employee VALUES (1, 6, 'manager', '01-01-02' , 20000.0000) INSERT into Employee VALUES (2, 7, 'editor', '01-01-02' , 10000.0000) INSERT into Employee VALUES (3, 8, 'manager', '01-01-02' , 20000.0000) INSERT into Supplier VALUES (1, 9, 'regular') INSERT into Supplier VALUES (2, 10, 'non regular') INSERT into Supplier VALUES (3, 1, 'regular') INSERT into Invoice VALUES (1, 1, 1, '01-01-02' , 'selling', 30000.0000) INSERT into Invoice VALUES (2, 1, NULL, '01-01-02' , 'buying', 20000.0000) INSERT into Invoice VALUES (3, 2, NULL, '01-01-02' , 'selling', 35000.0000) INSERT into Invoice VALUES (4, NULL, 2, '01-01-02' , 'buying', 40000.0000) INSERT into Invoice VALUES (5, NULL, 4, '01-01-02' , 'buying', 200.0000) INSERT into Item VALUES (1, 1, 'books', 100, 1999.0000, 2000.0000) INSERT into Item VALUES (2, 2, 'books', 101, 2999.0000, 3000.0000) INSERT into Item VALUES (3, 3, 'tables', 12, 20001.0000, 2500.0000) INSERT into Item VALUES (4, 1, 'tables', 30, 4000.0000, 4002.0000) INSERT into Item VALUES (5, 1, 'cars', 1, 20010.0000, 30000.0000)
