Raditya Rainard Kusuma

DATA & INFORMATION MANAGEMENT PROJECT “King Pangsit” DATABASE DESIGN Oleh: Sulistio Alvin Christian Nicholas Prathama Raditya Rainard Kusuma Chandra Gunawan - School of Information Systems BINUS University 2019 King Pangsit ➢ Company Profile • Berdiri pada tahun 2009 • Berlokasi di Jalan U Raya Blok U No. 2 Palmerah, Jakarta Barat • King Pansit bergerak dibidang penjualan makanan yakni contohnya Bakmie, Nasi goreng, dan lain lain. Struktur Organisasi Owner Waitress ➢ Business Process Chef ➢ Database Planning Visi : Menghindari terjadinya kerangkapan data (redundancy data). 2. Menghindari terjadinya inkonsistensi data. 3. Mengatasi kesulitan dalam mengakses data. 4. Menyusun format pendataan hasil dari penjualan. 1. Objektif : Menentukan kualitas informasi yaitu cepat, akurat, dan relevan. 2) Sebuah database bisa dimanfaatkan sekaligus secara bersama oleh banyak pengguna (multiuser). 3) Melakukan perlindungan dan pengamanan data. Setiap data hanya bisa diakses atau dimanipulasi oleh pihak yang diberi otoritas dengan memberikan login dan password terhadap masing-masing data. 4) Agar pemakai mampu menyusun suatu pandangan (view) abstraksi dari data. Hal ini bertujuan menyederhanakan interaksi antara pengguna dengan sistemnya dan database dapat mempresentasikan pandangan yang berbeda kepada para pengguna, programmer dan administratornya. 1) ➢ System Definition Tujuan kami membuat database di king pansit adalah untuk mempermudah pengecekan data penjualan makanan dari king pansit. Peran : • • • Owner : bertugas untuk mengawasi / mensupervise para staffnya. Staff : dibagi jadi dua yaitu chef dan waitress, tugas chef adalah memasak pesanan, tugas waitress adalah mencatat pesanan dan mengantarkan pesanan kepada member. Member : memesan dan membayar makanannya. ➢ Requirement Collection and Analysis We choose to use Centralize Approach for this database, because they’ll be able to: -Find information more quickly. -Have more accurate information. -Eliminate redundant files. -Have more effective collaboration with the team. But the primary function of a centralize database comes down to two things: either make decisions or take action. The decisions they make range from tactical decisions, like how frequently should we call this account to more strategic decisions around where to invest their marketing budget. One of the best ways to improve quality regardless of what they are working on is to implement a process. Everything from lead generation, data entry, creating new opportunities, sales processes, and the account management process will impact the quality of their database. The quality of your centralized database has a lot to do with how well organized it is. It displays the vital information front and center and is easily searchable. Conceptual Database Design Step 1.1 Identify Entity Types Data Dictionary of Entities Entity Name Staff Member Order Menu Owner Payment Payment type Description General term describing a staff employed by KingPangsit General term describing a registered customer into King Pangsit system General term describing a request from Member to King Pangsit General term describing a list of item which provided by King Pangsit General term describing an owner of King Pangsit General term describing payment which made by member to King Pangsit General term describing the type of payment which made by member to King Pangsit Aliases Employee Occurrence Each number of people works at King Pangsit Customer Each customer who registered by Staff Request Each request made by Member of King Pangsit Each item provided of King Pangsit Each owner of King Pangsit Payout Each order payment made in Pangsit King Payout type Each type of order payment in Pangsit King First-Cut ER Diagram Step 1.2 Identify Relationship Types Data Dictionary of Relationships Entity Name Member Order Staff Owner Payment Payment Type Menu Multiplicity 0..* 1..1 1..1 1..* 1..1 0..* 1..1 1..* 1..1 1..1 1..* 1..* Relationship Request Made Pay Contain Record Serve Work Supervise Pay AssociatedWith AssociatedWith DDescribe Multiplicity 1..1 0..* 1..1 1..* 1..* 1..1 1..* 1..1 1..1 1..* 1..1 1..1 Entity Name Order Member Payment Menu Staff Order Owner Staff Order Payment Type Payment Menu Step 1.3 Identify and associate attributes with entity or relationship types Data Dictionary of Attributes Entity Name Member Order Attribute Description MemberID MemberName Unique key identifies a member of Member Name of Member MemberGender Gender of Member MemberDOB MemberPhone MemberEmail Date of birth of Member Phone number of Member MemberAddress Email of Member OrderID MemberID StaffID PaymentID Menu OrderDate MenuID MenuName Staff MenuPrice StaffID Address of Member Unique key identifies a member of Order Foreign key identifies a member of Member Foreign key identifies a member of Staff Foreign key identifies a member of Payment Date of order Unique key identifies a member of Staff The name of Menu The price of Menu Unique key identifies a member of Staff Data Type & Length 5 Character Nulls MultiValued No No 30 variable character 1 character (M or F) Date 12 variable character 20 variable character 20 variable character No No Yes No Yes No No No No No No No 5 Character No No 5 Character No No 5 Character No No 5 Character No No Date 5 Character No No No No No No 20 Variable Character 10 Integer No 5 Character No No No Owner Payment StaffName Name of Staff StaffGender Gender of Staff StaffDOB StaffPhone Date of birth of Staff Phone number of Staff StaffEmail Email of Staff StaffAddress Address of Staff OwnerID OwnerName Unique key identifies a member of Owner Name of Owner OwnerGender Gender Owner OwnerDOB OwnerPhone OwnerEmail Date of Birth of Owner Phone number of Owner Email of Owner OwnerAddress Address of Owner PaymentID Unique key identifies a member of Payment Foreign key identifies a member of Payment Amount of Payment Status of Payment PaymentTypeID Amount Status Payment Type PaymentTypeID Unique key identifies a member of Payment type PaymentTypeName The name of payment type 30 variable character 1 character (M or F) Date 12 variable character 20 variable character 20 variable character 5 Character No No Yes No Yes No No No No No No No No No 30 variable character No No 1 character (M or F) Date 12 variable character 20 variable character 20 variable character 5 Character Yes No Yes No No No No No No No No No 5 Character No No 10 Integer 6 Variable character (Paid or Unpaid) 5 Character No No No No No No 15 Variable No character No Step 1.4 Determine attribute domains Entity Name Member Attribute Description Data Type Domain MemberID Character MB[0-9][0-9][0-9] MemberName Unique key identifies a member of Member Name of Member MemberGender Gender of Member MemberDOB Date of birth of Member Phone number of Member Date dd/mm/yyyy Variable Character 8-12 Digits Email of Member Variable character Variable character Character OR[0-9][0-9][0-9] Character MB[0-9][0-9][0-9] Character ST[0-9][0-9][0-9] Character PY[0-9][0-9][0-9] Date Character dd/mm/yyyy MN[0-9][0-9][0-9] MemberPhone Variable character Character MemberEmail MemberAddress Address of Member Order OrderID MenuName Unique key identifies a member of Order Foreign key identifies a member of Member Foreign key identifies a member of Staff Foreign key identifies a member of Payment Date of order Unique key identifies a member of Staff The name of Menu MenuPrice The price of Menu MemberID StaffID PaymentID Menu OrderDate MenuID Variable character Integer Staff Owner Payment StaffID StaffName Unique key identifies a member of Staff Name of Staff StaffGender Gender of Staff StaffDOB StaffPhone Date of birth of Staff Phone number of Staff StaffEmail Email of Staff StaffAddress Address of Staff OwnerID OwnerName Unique key identifies a member of Owner Name of Owner OwnerGender Gender Owner Character OwnerDOB OwnerPhone OwnerEmail Date of Birth of Owner Phone number of Owner Email of Owner OwnerAddress Address of Owner PaymentID Unique key identifies a member of Payment Foreign key identifies a member of Payment Amount of Payment Status of Payment Date Variable character Variable character Variable character Character PaymentTypeID Amount Status Payment Type PaymentTypeID Unique key identifies a member of Payment type PaymentTypeName The name of payment type Character ST[0-9][0-9][0-9] Variable character Character Date Variable character Variable character Variable character Character dd/mm/yyyy 8-12 Digits ON[0-9][0-9][0-9] Variable character dd/mm/yyyy 8-12 Digits PY[0-9][0-9][0-9] Character Integer Variable character Character Variable character PT[0-9][0-9][0-9] Step 1.5 Determine Primary Key, Candidate Key, Alternate Key Entity Name Member Primary Key Candidate Key Alternate Key MemberID MemberEmail, MemberPhone Order Menu Staff OrderID MenuID StaffID Owner OwnerID Payment Payment Type PaymentID PaymentTypeID MemberID, MemberEmail, MemberPhone OrderID MenuID, MenuName StaffID, StaffEmail, StaffPhone OwnerID, OwnerName,OwnerPhone PaymentID PaymentTypeID, PaymentTypeName Menu Name StaffEmail, StaffPhone OwnerName, OwnerPhone PaymentTypeName ER Diagram with Primary keys added Step 1.6 Consider use of enhanced modeling concepts Step 1.7 Check model for redundancy None Step 1.8 Validate Conceptual Model against user transactions a) Member could see their order which made by staff b) Staff could see Order so they can prepare member request c) Owner could access Staff profile information d) Member could access the Payment for their Order e) Member could access the Payment Type for their Order f) Member could access the Menu g) Owner could access the Member profile information h) Owner could access the information of Order i) Member could access few Staff profile information j) Staff could access few Member profile information Step 1.9 Review conceptual data model with user ER Conceptual Design Logical Database Step 2.1 Derive relations for logical data model 1)Strong entity types: The composition of the Owner relation is Owner(OwnerID, OwnerfName, OwnerlName, OwnerGender, OwnerDOB, OwnerPhone, OwnerEmail, OwnerAddress) Primary Key OwnerID Member(MemberID, MemberfName, MemberlName, MemberGender, MemberDOB, MemberPhone, MemberEmail, MemberAddress Primary Key MemberID Staff(StaffID, StafffName, StafflName, StaffGender, StaffDOB, StaffPhone, StaffEmail, StaffEmail, StaffAddress) Primary Key StaffID Payment(PaymentID, PaymentTypeID, Amount, status) Primary Key PaymentID Foreign Key PaymentTypeID PaymentType(PaymentTypeID, PaymenTypeName) Primary Key PaymentTypeID Menu(MenuID, MenuName, MenuPrice) Primary Key MenuID 2)Weak entity types: Order(OrderID, MemberID, StaffID, OrderDate) Primary Key(OrderID) Foreign Key(MemberID, StaffID, PaymentID) Order Detail(OrderID, MenuID, Quantity) Primary Key(OrderID, MenuID) Foreign Key(OrderID, MenuID) 3)Post OwnerID into Staff to model 1:* Supervise relationship Owner(OwnerID, OwnerName, OwnerFName,OwnerlName, OwnerGender, OwnerDOB, OwnerPhone, OwnerEmail, OwnerAddress) Primary Key OwnerID Staff(StaffID, StafffName, StafflName, StaffGender, StaffPhone, StaffEmail, StaffAddress,OwnerID) Primary Key StaffID Alternate Key StaffEmail Foreign Key OwnerID references Owner(OwnerID) 4)One-to-one (1:1) binary relationship types a.Mandatory participation on both sides of 1:1 relationship Order with Payment. In this case, we choose to merge the two relation together : Order(OrderID, PaymentID, MemberID, StaffID, OrderDate, PaymentTypeID, Amount, Status) b. Mandatory participation on one side of a 1:1 relationship if the 1:1 Staff Made Order relationship had partial participation on the Staff side (not every Staff specifies Order), then Staff become parent and the Order become child entity. So, we copy primary key from Staff to Order. Staff(StaffID, StafffName, StafflName, StaffGender, StaffPhone, StaffEmail, StaffAddress,OwnerID) Order(OrderID,MemberID, StaffID,PaymentID, OrderDate, PaymentTypeID, Amount, Status) 6) Representation of superclass / subclass relationship based on participation and disjointness Mandatory, disjoint Chef (StaffID, ChefExperience, StafffName, StafflName, StaffGender, StaffPhone, StaffEmail, StaffAddress, OwnerID) Primary Key StaffID Waitress(StaffID, WaitressPerfomance, StafffName, StafflName, StaffGender, StaffPhone, StaffEmail, StaffAddress, OwnerID) Primary Key StaffID 7) Many-to-many (*:*) binary relationship types From *:* Order contains Menu, bTo represent this relationship, we create a relation OrderDetail to represent the relationship contains: OrderDetail(OrderID, Menu ID, Quantity) Description: OrderID From Order entity MenuID from menu entity 9) Multi-valued attributes Orderdetail(OrderID, Menu ID, quantity) Primary Key (OrderID, MenuID) Foreign Key MenuID references Menu (MenuID), OrderID references Order (OrderID) Summary Relations for the Owner user views of King Pangsit Chef(staffID, staffName, staffGender, staffDOB, staffPhone, staffEmail, staffAddress, ChefExperience) Primary Key staffID Alternate Key staffPhone Waitress(staffID, staffName, staffGender, staffDOB, staffPhone, staffEmail, staffAddress,WaitressPerformance) Primary Key staffID Alternate Key staffPhone Owner(ownerID, ownerName, ownerGender, ownerDOB, ownerPhone, ownerEmail, ownerAddress) PrimaryKey ownerID Alternate Key ownerPhone Order(OrderID, MemberID, staffID,OrderDate, Amount, Status) Primary Key OrderID Foreign Key memberID references member(memeberID) StaffID references staff(staffID) paymentID references payment(paymentID) Member(memberID, memberName, memberGender, memberDOB, memberPhone, memberEmail, memberAddress) Primary Key memberID Alternate Key memberPhone Payment(PaymentID, paymentTypeID, amount, status) Primary Key paymentID Foreign Key paymentTypeID references paymentType(paymentTypeID) PaymentType(PaymentTypeID, paymentTypeName) Primary Key paymentTypeID Alternate Key PaymentTypeName OrderDetail(orderID,menuID,quantity) Primary Key orderID, menuID Foreign Key menuID references menu(menuID) Menu(MenuID, menuName, menuPrice) Primary Key MenuID Alternate Key MenuName Step 2.2 Validate relations using normalization Member 1NF MemberID, MemberName, MemberGender, MemberDOB, MemberPhone, MemberEmail 2NF MemberID, MemberName, MemberGender, MemberDOB, MemberPhone, MemberEmail 3NF MemberID, MemberName, MemberGender, MemberDOB, MemberPhone, MemberEmail Order 1NF OrderID, MemberID, StaffID, OrderDate, PaymentTypeID, amount, status 2NF OrderID, MemberID, StaffID, OrderDate, MemberName, MemberGender, MemberDOB, MemberPhone, MemberEmail, MemberAddress, StaffID, StaffName, StaffGender, StaffDOB, StaffPhone, PaymentTypeID, Amount,Status, PaymentTypeName 3NF Order: OrderID, MemberID, StaffID,PaymentID,OrderDate, Amount, Status Member: MemberID, MemberName, MemberGender, MemberDOB, MemberPhone, MemberEmail, MemberAddress Staff: StaffID, StaffName, StaffGender, StaffDOB, StaffPhone, StaffEmail, StaffAddress PaymentType: PaymentTypeID, PaymentTypeName Order Detail 1NF OrderID, MenuID, Quantity 2NF OrderID, MenuID, Quantity, MemberID, StaffID, PaymentTypeID, OrderDate, MenuName, MenuPrice 3NF Order Detail: OrderID, MenuID , Quantity Menu: MenuID, MenuName, MenuPrice Menu 1NF MenuID, MenuName, MenuPrice 2NF MenuID, MenuName, MenuPrice 3NF MenuID, MenuName, MenuPrice Waitress 1NF StaffID, StaffName, StaffGender, StaffDOB, StaffPhone, StaffEmail, StaffAddress, WaitressPerformance, OwnerID 2NF StaffID, StaffName, StaffGender, StaffDOB, StaffPhone, StaffEmail, StaffAddress, WaitressPerformance, OwnerID, OwnerID, OwnerName, OwnerGender, OwnerDOB, OwnerPhone, OwnerEmail, OwnerAddress 3NF Waitress: StaffID, StaffName, StaffGender, StaffDOB, StaffPhone, StaffEmail, StaffAddress, WaitressPerformance Owner: OwnerID, OwnerName, OwnerGender, OwnerDOB, OwnerPhone, OwnerEmail, OwnerAddress Chef 1NF StaffID, StaffName, StaffGender, StaffDOB, StaffPhone, StaffEmail, StaffAddress, ChefExperience, OwnerID 2NF StaffID, StaffName, StaffGender, StaffDOB, StaffPhone, StaffEmail, StaffAddress, ChefExperience, OwnerID, OwnerID, OwnerName, OwnerGender, OwnerDOB, OwnerPhone, OwnerEmail, OwnerAddress 3NF Chef: StaffID, StaffName, StaffGender, StaffDOB, StaffPhone, StaffEmail, StaffAddress, ChefExperience Owner: OwnerID, OwnerID, OwnerName, OwnerGender, OwnerDOB, OwnerPhone, OwnerEmail, OwnerAddress Owner 1NF OwnerID, OwnerName, OwnerGender, OwnerDOB, OwnerPhone, OwnerEmail, OwnerAddress 2NF OwnerID, OwnerName, OwnerGender, OwnerDOB, OwnerPhone, OwnerEmail, OwnerAddress 3NF OwnerID, OwnerName, OwnerGender, OwnerDOB, OwnerPhone, OwnerEmail, OwnerAddress Payment Type 1NF PaymentTypeID, PaymentTypeName 2NF PaymentTypeID, PaymentTypeName 3NF PaymentTypeID, PaymentTypeName Step 2.3 Validate relations against user transactions a) Member could see their order which made by staff b) Chef could see Order so they can prepare member request c) Owner could access Staff profile information d) Member could access the Payment for their Order e) Member could access the Payment Type for their Order f) Member could access the Menu g) Owner could access the Member profile information h) Owner could access the information of Order i) Member could access few Staff profile information j) Waitress could see few Member profile information Step 2.4 Check integrity constraints Member (MemberID, MemberName, MemberGender,MemberDOB,MemberPhone,MemberEmail,MemberAddress) Primary Key MemberID Candidate Key MemberPhone, MemberEmail Alternate Key MemberPhone, MemberEmail Order (OrderID,MemberID,StaffID,PaymentTypeID,OrderDate,Amount,Status) Primary Key OrderID Foreign Key MemberID references Member(MemberID) ON UPDATE CASCADE ON DELETE SET NULL Foreign Key StaffID references Staff(StaffID) ON UPDATE CASCADE ON DELETE SET NULL Foreign Key PaymentTypeID references PaymentType(PaymentTypeID) ON UPDATE CASCADE ON DELETE SET NULL OrderDetail (OrderID,MenuID,Quantity) Primary Key OrderID, MenuID Foreign Key MenuID references Menu(MenuID) ON UPDATE CASCADE ON DELETE NO ACTION Foreign Key OrderID references Order(OrderID) ON UPDATE CASCADE ON DELETE SET NULL Menu (MenuID,MenuName,MenuPrice) Primary Key MenuID Alternate Key MenuName Candidate Key MenuName Waitress (StaffID,StaffName,StaffGender,StaffDOB,StaffPhone,StaffEmail,StaffAddress,WaitressPerfor mance) Primary Key StaffID Candidate Key StaffPhone, StaffEmail Alternate Key StaffEmail Chef (StaffID,StaffName,StaffGender,StaffDOB,StaffPhone,StaffEmail,StaffAddress, xperienceChefE) Primary Key StaffID Candidate Key StaffPhone, StaffEmail Alternate Key StaffEmail Owner (OwnerID,OwnerName,OwnerGender,OwnerDOB,OwnerPhone,OwnerEmail,OwnerAddress) Primary Key OwnerID Candidate Key OwnerPhone Candidate Key OwnerEmail Alternate Key OwnerEmail PaymentType (PaymentTypeID,PaymentTypeName) Primary Key PaymentTypeID Alternate Key PaymentTypeName Step 2.5 Review logical data model with user Step 2.6 Merge logical data models into global model (optional) ER Logical Model Physical Database Step 3 Translate logical data model for target DBMS • Step 3.1 Design base relations CREATE TABLE Member( MemberID CHAR (5) NOT NULL CONSTRAINT [mID] CHECK (MemberID LIKE 'ME[0-9][0-9][0-9]') PRIMARY KEY, MemberName VARCHAR (30) NOT NULL, MemberGender CHAR (1) NOT NULL CONSTRAINT [mGender] CHECK (MemberGender = 'M'or MemberGender ='F'), MemberDOB DATE, MemberPhone VARCHAR (12) NOT NULL CONSTRAINT [mPhone] CHECK (LEN(MemberPhone)>=8 AND LEN(MemberPhONe)<=12), MemberEmail VARCHAR (20) NOT NULL, MemberAddress VARCHAR (40) NOT NULL ); CREATE TABLE PaymentType( PaymentTypeID CHAR (5) NOT NULL CONSTRAINT [pID] CHECK (PaymentTypeID LIKE 'PT[0-9][09][0-9]') PRIMARY KEY, PaymentTypeName VARCHAR (10) NOT NULL ); CREATE TABLE Menu( MenuID CHAR (5) NOT NULL CONSTRAINT [mnID] CHECK (MenuID LIKE 'MN[0-9][0-9][0-9]') PRIMARY KEY, MenuName VARCHAR (30) NOT NULL, MenuPrice INT NOT NULL ); CREATE TABLE [Owner]( OwnerID CHAR (5) NOT NULL CONSTRAINT [oID] CHECK (OwnerID LIKE 'ON[0-9][0-9][0-9]') PRIMARY KEY, OwnerName VARCHAR (30) NOT NULL, OwnerGender CHAR (1) NOT NULL CONSTRAINT [oGender] CHECK (OwnerGender = 'M'or OwnerGender ='F'), OwnerDOB DATE, OwnerPhone VARCHAR (12) NOT NULL CONSTRAINT [oPhone] CHECK (LEN(OwnerPhone)>=8 AND LEN(OwnerPhone)<=12), OwnerEmail VARCHAR (30) NOT NULL, OwnerAddress VARCHAR (30) NOT NULL ); CREATE TABLE Waitress( StaffID CHAR (5) NOT NULL CONSTRAINT [wID] CHECK (StaffID LIKE 'ST[0-9][0-9][0-9]') PRIMARY KEY, OwnerID CHAR (5) NOT NULL REFERENCES Owner(OwnerID), StaffName VARCHAR (30) NOT NULL, StaffGender CHAR (1) NOT NULL CONSTRAINT [sGender] CHECK (StaffGender = 'M'or StaffGender ='F'), StaffDOB DATE, StaffPhone VARCHAR (12) NOT NULL CONSTRAINT [sPhone] CHECK (LEN(StaffPhone)>=8 AND LEN(StaffPhone)<=12), StaffEmail VARCHAR (30) NOT NULL, WaitressPerformance INT NOT NULL ); CREATE TABLE Chef( StaffID CHAR (5) NOT NULL CONSTRAINT [cID] CHECK (StaffID LIKE 'ST[0-9][0-9][0-9]') PRIMARY KEY, OwnerID CHAR (5) NOT NULL REFERENCES Owner(OwnerID), StaffName VARCHAR (30) NOT NULL, StaffGender CHAR (1) NOT NULL CONSTRAINT [s2Gender] CHECK (StaffGender = 'M'or StaffGender ='F'), StaffDOB DATE, StaffPhone VARCHAR (12) NOT NULL CONSTRAINT [s2Phone] CHECK (LEN(StaffPhone)>=8 AND LEN(StaffPhone)<=12), StaffEmail VARCHAR (30) NOT NULL, ChefExperience VARCHAR (10) NOT NULL ); CREATE TABLE [Order]( OrderID CHAR (5) NOT NULL CONSTRAINT [orID] CHECK (OrderID LIKE 'OR[0-9][0-9][0-9]') PRIMARY KEY, MemberID CHAR (5) NOT NULL FOREIGN KEY REFERENCES Member(MemberID), StaffID CHAR (5) NOT NULL FOREIGN KEY REFERENCES Waitress(StaffID), PaymentTypeID CHAR (5) NOT NULL FOREIGN KEY REFERENCES PaymentType(PaymentTypeID), Orderdate DATE, Amount INT NOT NULL, [Status] VARCHAR (5) NOT NULL ); CREATE TABLE OrderDetail( OrderID CHAR (5) NOT NULL CONSTRAINT [or2ID] CHECK (OrderID LIKE 'OR[0-9][0-9][0-9]'), MenuID CHAR (5) NOT NULL CONSTRAINT [m2ID] CHECK (MenuID LIKE 'MN[0-9][0-9][0-9]'), Quantity INT NOT NULL PRIMARY KEY (OrderID, MenuID), FOREIGN KEY (OrderID) REFERENCES [Order](OrderID), FOREIGN KEY (MenuID) REFERENCES Menu(MenuID) ); • Step 3.2 Design representation of derived data --Untuk menampilkan semua daftar sejarah transaksi Select o.OrderID as 'OrderID', CONVERT (NVARCHAR, o.OrderDate, 101) as 'Date', pt.PaymentTypeName as 'PaymentType', CONCAT(SUM (od.quantity * m.MenuPrice),'.000') as 'Subtotal' From [Order] o join OrderDetail od on o.OrderID = od.OrderID join Menu m on od.MenuID = m.MenuID join PaymentType pt on o.PaymentTypeID= pt.PaymentTypeID Group by o.OrderID, Orderdate, PaymentTypeName --Untuk menampilkandaftar sejarah transaksi dengan syarat pada tanggal 1, bulan 1 (januari), tahun 2020 dan dengan tipe pembayaran OVO Select o.OrderID as 'OrderID', CONVERT (NVARCHAR, o.OrderDate, 101) as 'Date', pt.PaymentTypeName as 'PaymentType', CONCAT(SUM (od.quantity * m.MenuPrice),'.000') as 'Subtotal' From [Order] o join OrderDetail od on o.OrderID = od.OrderID join Menu m on od.MenuID = m.MenuID join PaymentType pt on o.PaymentTypeID= pt.PaymentTypeID WHERE DAY (o.Orderdate)=1 AND MONTH(o.Orderdate)=1 AND Year(o.Orderdate) =2020 AND pt.PaymentTypeName='OVO' Group by o.OrderID, Orderdate, PaymentTypeName --Untuk memasukan data order kedalam database INSERT INTO [Order](OrderID, MemberID, StaffID, PaymentTypeID, OrderDate, Amount, [Status]) VALUES ('OR017', (SELECT MemberID FROM Member WHERE MemberName='Susan'), (SELECT StaffID FROM Waitress WHERE StaffName= 'Ayu'), (SELECT PaymentTypeName FROM PaymentType WHERE PaymentTypeName= 'OVO'), GETDATE(), (Select CONCAT(SUM (od.quantity * m.MenuPrice),'.000') as 'Total Price' From [Order] o join OrderDetail od on o.OrderID = od.OrderID join Menu m on od.MenuID = m.MenuID WHERE o.OrderID ='OR017'), 'SUDAH'); --Untuk menunjukan daftar nama menu dan jumlahnya serta menunjukan total harga Select m.MenuName, od.Quantity, CONCAT(SUM (od.quantity * m.MenuPrice),'.000') as 'Total Price' From OrderDetail od join Menu m ON od.MenuID = m.MenuID GROUP BY m.MenuName, od.Quantity --Untuk menunjukan jumlah harga dari menu dan jumlah yang dimuat pada order detail Select CONCAT(SUM (od.quantity * m.MenuPrice),'.000') as 'Total Price' From [Order] o join OrderDetail od on o.OrderID = od.OrderID join Menu m on od.MenuID = m.MenuID WHERE o.OrderID ='OR017' Step 4.1 Analyze transactions Transaction Analysis Form Transaction 14-Jan-2020 (A)View the PaymentID,PaymentTypeID and Amount into a payment type table Transaction volume Average: Peak: 12 per hour 20 per hour(Between 13.00 and 19.00 Monday-Sunday) SELECT PaymentID, PaymentTypeID FROM Payment JOIN PaymentType WHERE PaymentID = ‘Tipe Pembayaran’ Predicate: PaymentID = ‘Tipe Pembayaran’ Join attributes: PaymentTypeID Transaction usage map Access 1 2 Entity Types of access Payment R Payment Type R Total References No. of References Per Transaction Avg Per Hour Peak Per Hour- 12 24 20 40 Transaction Analysis Form Transaction 14-Jan-2020 (B) List Order id for staff Transaction volume Average: Peak: 10 per 20 hour 12 per 20 hour(Between 13.00 and 19.00 Monday-Sunday) Predicate: OrderID = ‘Pemesanan’ SELECT OrderID, StaffID FROM Order WHERE OrderID = ‘Pemesanan’ Transaction usage map Access 1 2 Entity Types of access Order R Staff R Total References No. of References Per Transaction Avg Per Hour Peak Per Hour- Transaction Analysis Form Transaction 14-Jan-2020 (C)List all atribute from staff for owner Transaction volume Average: Peak: 8 per hour 30 per hour(Between 17.00 and 19.00 Monday-Sunday) SELECT StaffID,StaffName,StaffGender,StaffDOB, StaffPhone,StaffEmail,StaffAddress FROM Staff WHERE StaffID = ‘Profile Staff’ Predicate: StaffID = ‘Profile Staff’ Transaction usage map Access 1 2 Entity Types of access Staff I Owner R Total References No. of References Per Transaction Avg Per Hour Peak Per Hour- Transaction Analysis Form Transaction 14-Jan-2020 (D) List Payment for member Transaction volume Average: Peak: 25 per hour 30 per hour(Between 13.00 and 19.00 Monday-Sunday) SELECT PaymentID,PaymentTypeID,Amount FROM Payment WHERE PaymentID = ‘Biaya Pembayaran’ Predicate: PaymentID = ‘Biaya Pembayaran’ Transaction usage map Access 1 2 Entity Types of access Payment R Order R Total References No. of References Per Transaction Avg Per Hour Peak Per Hour- Transaction Analysis Form Transaction 14-Jan-2020 (E) List all Menu into a table for member who wants to order Transaction volume Average: Peak: 15 per hour 30 per hour(Between 13.00 and 19.00 Monday-Sunday) SELECT MenuID,MenuName,MenuPrice FROM Menu WHERE MenuID = ‘Menu’ Predicate: MenuID = ‘Biaya Pembayaran’ Transaction usage map Access 1 2 Entity Types of access Menu R Member R Total References No. of References Per Transaction Avg Per Hour Peak Per Hour- Transaction Analysis Form Transaction 14-Jan-2020 (F) List Member Profile for Owner Transaction volume Average: Peak: 15 per hour 30 per hour(Between 13.00 and 19.00 Monday-Sunday) SELECT Predicate: MemberID = ‘Profile Member’ MemberID,MemberName,MemberfName, MemberlName,MemberGender,MemberDOB, MemberPhone,MemberEmail,MemberAddress FROM Member WHERE MemberID = ‘Profile Member’ Transaction usage map Access 1 2 Entity Types of access Member R Owner R Total References No. of References Per Transaction Avg Per Hour Peak Per Hour- Transaction Analysis Form Transaction 14-Jan-2020 (G) List order for chef Transaction volume Average: Peak: 12 per hour 24 per hour(Between 13.00 and 19.00 Monday-Sunday) Predicate: OrderID= ‘Order List’ SELECT OrderID,MemberID FROM Order WHERE OrderID = ‘Order List’ Transaction usage map Access 1 2 Entity Types of access Menu R Member R Total References No. of References Per Transaction Avg Per Hour Peak Per Hour- Transaction Analysis Form Transaction 14-Jan-2020 (H) List all order for owner Transaction volume Average: Peak: 10 per hour 20 per hour(Between 13.00 and 19.00 Monday-Sunday) SELECT OrderID,MemberID,StaffID,OrderDate FROM Order WHERE OrderID = ‘Order List’ Predicate: OrderID = ‘Order List’ Transaction usage map Access 1 2 Entity Types of access Menu R Member R Total References No. of References Per Transaction Avg Per Hour Peak Per Hour- Transaction Analysis Form Transaction 14-Jan-2020 (I) List Staff few profile for member Transaction volume Average: Peak: 10 per hour 20 per hour(Between 13.00 and 19.00 Monday-Sunday) SELECT StaffName,StaffGender, FROM Staff WHERE StaffName = ‘Profile Staff’ Predicate: StaffName = ‘Profile Staff’ Transaction usage map Access 1 2 Entity Types of access Staff R Member R Total References No. of References Per Transaction Avg Per Hour Peak Per Hour- Transaction Analysis Form Transaction 14-Jan-2020 (J)List the Waitress Performance for Owner Transaction volume Average: Peak: 1 per 2 hour 1 per 2 hour(Between 17.00 and 19.00 Monday-Saturday) SELECT WaitressPerformance FROM Waitress JOIN Staff WHERE WaitressPerformance = ‘KPI’ Predicate: WaitressPerformance = ‘KPI’ Join attributes: Waitress JOIN Staff Transaction usage map Access 1 2 Entity Types of access Owner(entry) Store Total References R R No. of References Per Transaction Avg Per Hour Peak Per Hour- STEP 4.4 Estimate Disk Space Requirement • Next hardware cycle: 3 years • Current database size: 2013 MB • Current full backup size (week N): 1177 MB • Previous full backup size (week N-1): 936 MB • Delta size: 241MB per week • Delta ratio: 25.7% increment per week • Total weeks in 3 years: 156 weeks • Total database size estimation: ((1177 - 936) x 2013 x 156)/936 = 80856 MB ~ 81 GB after 3 years • Step 3.3 Design general constraint Step 4 Design file organization and indexes • • Step 4.2 Choose file organizations Step 4.3 Choose indexes Step 5 Design user views Step 6 Design security mechanisms Step 7 Consider the introduction of controlled redundancy Step 8 Monitor and tune operational system Kami berencana untuk datang ke King Pangsit setiap 3 bulan sekali untuk melihat dan memonitori perkembangan data pada database berserta sistem yang menjalankan database King Pangsit.. Jika terjadi sesuatu kesalahan atau sesuatu yang tidak diinginkan maka, kami akan ebrusahan untuk membeantu pemilik King Pangsit dalam menyelesaikan kesalahan atau masalah yang terjadi. Prototype Prototype Strategy: Requierment Prototyping Strategi Prototyping yang kami pilih yakni, Requirements Prototyping, karena kami menggunakan prototype berupa aplikasi yang bernama axure, yang berguna untuk menentukan persyaratan pada sistem database yang diusulkan, kemudian setelah persyaratan telah selesai ditentukan maka prototype akan dibuang dan digantikan dengan aplikasi atau database yang benar-benar jadi. 1. Plan Prototype Visi : Menjadikan prototype ini berguna untuk menentukan persyaratan pada sistem database yang diperlukan. Misi : - Membuat sebuah prototype untuk database kingpangsit menggunakan aplikasi axure. - Membuat design dari UI aplikasi database kingpangsit yang dapat digunakan oleh owner dan member. - Mengidentifikasi data-data yang akan digunakan atau di masukan ke dalam database kingpangsit. 2. Refine Objectives 3. Identify data to be used INSERT INTO Member(MemberID, MemberName, MemberGender, MemberDOB, MemberPhone, MemberEmail, MemberAddress) VALUES ('ME001', 'Michael', 'M', '-', '-',-, 'Soekarno Hatta Street'), ('ME002',' Stephanie', 'F', '-', '-',-, 'Sudirman Street'), ('ME003', 'Susan', 'F', '-', '-',-, 'Rawa Belong Street'), ('ME004', 'Leonardo', 'M', '-', '-',-, 'Kemanggisan Street'), ('ME005', 'Luis', 'M', '-', '-',-, 'Suka Makmur Street'), ('ME006', 'Felice', 'F', '-', '-',-, 'Jaya Street'); INSERT INTO PaymentType(PaymentTypeID, PaymentTypeName) VALUES ('PT001', 'GO-Pay'), ('PT002', 'OVO'), ('PT003', 'Cash'); INSERT INTO Menu( MenuID, MenuName, MenuPrice) VALUES ('MN001', 'Bakmie Pok', 20.000), ('MN002', 'Bakmie Pok Pansit Rebus', 24.000), ('MN003', 'Bakmie Babi Kecap', 26.000), ('MN004', 'Bakmie Pok Komplit', 28.000), ('MN005', 'Pansit Rebus 8pcs', 20.000), ('MN006', 'Nasi Goreng Babi', 25.000), ('MN007', 'Nasi Gila Babi', 25.000), ('MN008', 'Nasi Babi Kecap', 25.000), ('MN009', 'Kwetiau Goreng Special', 25.000), ('MN010', 'Bakmie Goreng Special', 25.000), ('MN011', 'Teh Tawar', 3.000), ('MN012', 'Teh Manis', 5.000), ('MN013', 'Kopi Susu', 7.000), ('MN014', 'Lemon Tea', 8.000), ('MN015', 'Milo', 9.000); INSERT INTO [Owner] (OwnerID, OwnerName, OwnerGender, OwnerDOB, OwnerPhone, OwnerEmail, OwnerAddress) VALUES ('ON001', 'Lilyana', 'F', '-', '-',-, 'U Raya Street'); INSERT INTO Waitress( StaffID, StaffName, OwnerID, StaffGender, StaffDOB, StaffPhone, StaffEmail, WaitressPerformance) VALUES ('ST002', 'Ayu', 'ON001', 'F', '-', '-',-, 8), ('ST003', 'Putri', 'ON001', 'F', '-', '-',-, 9); INSERT INTO Chef( StaffID, StaffName, OwnerID, StaffGender, StaffDOB, StaffPhone, StaffEmail, ChefExperience) VALUES ('ST001', 'Kurniawan', 'ON001', 'M', '-', '-',-, '3 Years'); INSERT INTO [Order] (OrderID, MemberID, StaffID, PaymentTypeID, OrderDate, Amount, [Status]) VALUES ('OR001', 'ME002', 'ST003', 'PT003', '-', 56.000, 'SUDAH'), ('OR002', 'ME005', 'ST003', 'PT001', '-', 50.000, 'SUDAH'), ('OR003', 'ME003', 'ST002', 'PT001', '-', 30.000, 'SUDAH'), ('OR004', 'ME005', 'ST003', 'PT002', '-', 40.000, 'SUDAH'), ('OR005', 'ME001', 'ST002', 'PT003', '-', 65.000, 'SUDAH'), ('OR006', 'ME004', 'ST002', 'PT002', '-', 7.000, 'SUDAH'), ('OR007', 'ME006', 'ST003', 'PT001', '-', 50.000, 'SUDAH'); INSERT INTO OrderDetail (OrderID, MenuID, Quantity) VALUES ('OR001', 'MN004', 2), ('OR002', 'MN007', 1), ('OR002', 'MN008', 1), ('OR003', 'MN006', 1), ('OR003', 'MN012', 1), ('OR004', 'MN001', 2), ('OR005', 'MN005', 1), ('OR005', 'MN009', 1), ('OR005', 'MN010', 1), ('OR006', 'MN013', 1), ('OR007', 'MN010', 2); Develop Prototype Member View 1) Login Page 2) Sign Up Page 3) Homepage Owner View Menu Food Menu Drink Menu Payment Menu Finnance