POSTS AND TELECOMMUNICATIONS INSTITUTE OF TECHNOLOGY INFORMATION TECHNOLOGY DEPARTMENT COURSEWORK Distributed Database Systems Discussion: BUILDING A DISTRIBUTED DATABASE SYSTEM TO MANAGE SEAFOOD COMPANY Lecturer: PhD. Nguyen Dinh Hoa Team: 08 Class: E18CQCN01-B Hanoi, Nov 2021 1 POSTS AND TELECOMMUNICATIONS INSTITUTE OF TECHNOLOGY DEPARTMENT OF INFORMATION TECHNOLOGY COURSEWORK Distributed Database Systems Team 08 Full name Student num. Nguyễn Thành Đạt (Leader) B18DCCN141 Hà Quốc Huy B18DCCN263 Trần Huy Nam B18DCDT171 Hanoi - 2021 Contents I. Real Worlf Scenario.
Importance of the project. Benefits of using distributed system. The main functions of the system. 7 • Data management of materials.
7 • Manage warehouse information. 7 • Manage employee information. 7 • Manage warehouse import and export information. 7 • Manage customer information.
7 • Provider information management. Function of each position. Function at workstations (branch warehous: Thai Binh, Nam Dinh). Functions at the server (headquater: Hanoi).
Decentralize system object. Linked enity model. Frequency table of accessing locations. List of tables.
Horizontal fragmentation of data. Vertical fragmentation of data. Install SQL server. Setting Radmin VPN.
Create Shared Folder. Database settings and fragmentation. Connect, Decentralize – ensure transparency. Configure the “sa” account.
Create a read-only account. Show all Customer from Ha Noi 2 (HN2SERVER) from another branch. Show Name and Numer of top 10 Customer from Nam Dinh (ND2SERVER). Show top 10 Employee at Thai Binh from HN2SERVER.
Insert data from Ha Noi (HNSERVER) and test the synchronization in Ha Noi 2 (HN2SERVER). Update Customer from Ha Noi (HNSERVER) and test the synchronization. Find out a customer whose number phone is 0988585568 to test the Connection. Show all Invoice and Sort by Total Price.
Concurrency & Commit Protocols. One-phase Commit. Two-Phase Commit. Concurrency Problem Types.
Transaction isolation level. Two Phase Locking – 2PL. Strict strong Two Phase Locking (SS2PL). Two transactions entered in a non-serializable order.
Two transactions affect on the same tables -different rows. Read/write conflict. Write/write conflict. Distributed Failure/Recovery.
Types of failures:. Correct recovery after a commit. Correct recovery before a commit. A server site is crashed.
Real Worlf Scenario 1. Importance of the project The storage of large amounts of information is a dilemma in the process of operating retail chains such as clothing, food, etc., especially for seafood store management chains. A system that needs to store a lot of information such as: employee information, product information,. causes difficulties in the process of managing, comparing information and prone to errors.
In the case of a seafood store, checking the amount of items in stock is necessary to make decisions in import and export, activities that directly affect the financial resources of the store. Thus, the bookkeeping method has many limitations. To facilitate management, it is necessary to build a digital system and digitize all necessary information to be stored into one system. With the high consumption demand of people throughout the provinces, it is necessary to open more branches.
At this time, data management by a centralized basis will reveal many weaknesses. Since the data is stored at only one main server, then when the branches need to retrieve information, they will have to send requests there, waiting for a response, which takes a long time. In addition, when new data is updated in large quantities, the server must process a lot, take time and may lose information. In fact, each branch will have the need to manage its own type of information and data, because the amount of data at each branch is very large, so it is reasonable to use a distributed database system here.
Benefits of using distributed system - Staff: Easily check goods information. Import and export management suppliers - Economical: Reduce operating costs, save costs on network, maintenance, inspection and data recovery as well as time to fulfil requests - Stores: Make the management simple, fast, convenient and improve work efficiency 6 II. The main functions of the system • Data management of materials • Manage warehouse information • Manage employee information • Manage warehouse import and export information • Manage customer information • Provider information management 2. Function of each position 2.
Function at workstations (branch warehous: Thai Binh, Nam Dinh) - Manage information of object belonging to the branch: add, delete detail of branch warehouse, employees, customers, raw materials, inventory, import- export. - Statistics function by monthly revenue 2. Functions at the server (headquater: Hanoi) • The entire function of the branch, • Manage branch information: can add, edit, delete detailed information of branches and warehouses belonging to branches, • Manage material information: Add, edit, delete information of goods, raw materials, • Statistical reporting function: revenue statistics, import-export statistics, etc. Decentralize system object • Management staff (at the server): can view, add, edit, delete all data information • Staff at the branch: o Information can be viewed at the branch: branch information, branch warehouse information, customers, employees, raw materials, inventory, import-export at the branch, 7 o Can add, edit, delete import and export information, employees, customers at the branch.
Linked enity model 8 3. Frequency table of accessing locations Entity Headquater Branch BRANCH H.CRU INVOICE_DETAIL H.CRU RECEIPT_NOTE H.R C (Create): Add data H (High): High frequency R (Read): Read L (Low): Low frequency U (Update): Edit data D (Delete): Delete data 9 III. Overall design Seafood company has a head office in Hanoi and 2 branches in Thai Binh and Nam Dinh Distribute the database CTHS (Seafood Company) into 3: Server 1 in Thai Binh: contains information about employees, vouchers/invoices, raw materials and data generated at Thai Binh branch Server 2 in Nam Dinh: contains information about employees, vouchers/invoices, raw materials and data generated at Nam Dinh branch Server 3 in Hanoi 2: contains employee information, raw materials, suppliers, coupons/invoices, warehouse information of both branches. Server in Hanoi: contains employee information, raw materials, suppliers, coupons/ invoices, warehouse information of both branches.
- Deployment management software with one server (manager) and 3 workstations. The server aggregates data and coordinates data between substation 10 - Station 1, Station 2, Station 3 are located in different places, in stations containing different fragmented data, operating under the same system, linked together via communication network/intranet - Each station is independent of the other, having a data connection with each other to request data from another station. All 3 stations have a connection to the server, when there is a change in data from the 3 stations, they are instantly synchronized to the server. Head office: Server (Hanoi) - Server user: Restaurant chain owner - Data: employee information, suppliers, raw materials, vouchers/invoices, warehouses - Input data: is entered at the server or sent from the workstations - Output data: saved at the server, and updated at the workstations Workstation: (1) Thai Binh, (2) Nam Dinh, (3) Ha Noi 2 - User: Manager at restaurant branches - Data: employee information, material information, vouchers/invoices - Input data: sent down from the server or manually entered by the employee.
- Output data: saved at the workstation and updated to the server's database. List of tables BRANCH TABLE Contains information about the company's branches Entity Type data Constraint id_bra nchar(25) Primary key name_bra nvarchar(255) Unique addr nvarchar(255) 11 num nchar(25) STORE TABLE Contains information related to the repositories Entity Type data Constraint id_sto nchar(25) Primary key name_sto nvarchar(255) Unique addr nvarchar(255) id_bra nchar(25) Foreign Key, ON UPDATE CASCADE CUSTOMER TABLE Contains information related to customers Entity Type data Contraint id_cus nchar(25) Primary key fullname nvarchar(255) addr nvarchar(255) num nchar(25) id_bra nchar(25) Foreign Key, ON UPDATE CASCADE EMPLOYEE TABLE Contrains emplyee information Entity Type data Contraint id_emp char(25) Primary key fullname nvarchar(255) age int addr nvarchar(255) sal float > 5,000,000.0 id_bra nchar(25) Foreign Key, ON UPDATE CASCADE SEAFOOD TABLE Contains information related to materials – products 12 Entity Type data Contraint id_sea char(25) Primary key name_sea nvarchar(255) price float supplỉer nvarchar(255) inStock float > 0.0 INVOICE TABLE Contains invoice related information Entity Type data Contraint id_inv int Primary key Time_date smalldatetime DEFAULT(GETDATE()) totalPrice float id_cus nchar(25) Foreign Key id_emp nchar(25) Foreign Key id_sto nchar(25) Foreign Key, ON UPDATE CASCADE INVOICE_DETAIL TABLE Contains information related to invoice details Entity Type data Contraint id_inv int Primary key, Foreign Key, ON UPDATE CASCADE id_sea char(25) Primary key, Foreign Key, ON UPDATE CASCADE amount float > 0.0 RECEIPT_NOTE TABLE Contains information related to the entry form Entity Type data Contraint id_rec int Primary key date_time date DEFAULT(GETDATE()) source nvarchar(255) totalPrice float id_emp char(25) Foreign Key id_sto nchar(25) Foreign Key, ON UPDATE CASCADE 13 RECEIPT_DETAIL TABLE Contains information related to entry slip details Entity Type data Contraint id_rec int Primary key, Foreign Key, ON UPDATE CASCADE id_sea char(25) Primary key, Foreign Key, ON UPDATE CASCADE Amount float > 0. Diagram Because the branches can manage the information in a complete table like in the main system. It is recommended to use horizontal fragmentation to disperse the data so that the physical structure of the data tables at the stations is the same.
System data structure: 3. Horizontal fragmentation of data BRANCH tables will be used by all sites, but can only be updated, edited or deleted on the master server. 15 The tables EMPLOYEE, STORAGE, CUSTOMER, INVOICE, INVOICE_DETAIL, SEAFOOD, RECEIPT_NOTE, RECEIPT_DETAIL will be used separately at each site. Primitive horizontal fragmentation and derived fragmentation divide the overall relationship into 3 pieces located at 3 branches: • Location 1: Server located in Hanoi • Location 2: Workstation 1 is located in Thai Binh • Location 3: Workstation 2 is located in Nam Dinh For fragmentation, we choose the overall relation BRANCH as the criterion for horizontal fragmentation into 2 located at 2 locations, then based on those fragments to defragment derivatives to the remaining relations.
Fragmentation Piece Server Primitive horizontal Derived horizontal fragmentation Fragmentation table: BRANCH Fragmentation table: CUSOMTER BRANCH1 = CUSTOMER1 = 𝜎𝐼𝐷_𝐵𝑅𝐴=′𝐵𝑅𝐴1′ (𝐵𝑅𝐴𝑁𝐶𝐻 ) CUSTOMER ▷◁ BRANCH1 Fragmentation table: EMPLOYEE EMPLOYEE1 = EMPLOYEE ▷◁ BRANCH1 Fragmentation table: STORAGE Thai 1 STORAGE1 = Binh(1) STORAGE ▷◁ BRANCH1 Fragmentation table: INVOICE INVOICE1 = INVOICE ▷◁ STORAGE1 Fragmentation table: INVOICE_DETAIL INVOICE_DETAIL1 = INVOICE_DETAIL ▷◁ INVOICE1 16 Fragmentation table: RECEIPT_NOTE RECEIPT_NOTE1 = RECEIPT_NOTE ▷◁ STORAGE1 Fragmentation table: RECEIPT_DETAIL RECEIPT_DETAIL1 = RECEIPT_DETAIL ▷◁ RECEIPT_NOTE1 Fragmentation table: BRANCH Fragmentation table: CUSOMTER BRANCH2 = CUSTOMER2 = 𝜎𝐼𝐷𝐵𝑅𝐴=′𝐵𝑅𝐴2′ (𝐵𝑅𝐴𝑁𝐶𝐻) CUSTOMER ▷◁ BRANCH2 Fragmentation table: EMPLOYEE EMPLOYEE2 = EMPLOYEE ▷◁ BRANCH2 Fragmentation: STORAGE STORAGE2 = STORAGE ▷◁ BRANCH2 Nam Fragmentation table: INVOICE 2 INVOICE2 = Định (2) INVOICE ▷◁ STORAGE2 Fragmentation table: INVOICE_DETAIL INVOICE_DETAIL2 = INVOICE_DETAIL ▷◁ INVOICE2 Fragmentation table: RECEIPT_NOTE RECEIPT_NOTE2 = RECEIPT_NOTE ▷◁ STORAGE2 Fragmentation table: RECEIPT_DETAIL RECEIPT_DETAIL2 = RECEIPT_DETAIL ▷◁ RECEIPT_NOTE2 3. Mapping Schema At the server, the global relationship via horizontal fragmentation is split into 2 fragments CHHS_BRA1 and CHHS_BRA2. Each piece has only 1 copy at a certain tramh 17 3. Vertical fragmentation of data Vertical fragmentation of CUSTOMER, EMPLOYEE, BRANCH tables IV.
Install SQL server Installed on all machines All machines need to install SQL server 2019. Download here: https://www.com/en-us/sql-server/sql-server-downloads Note: download developer or enterprise version, do not download Express version 18 After downloading the file: We use the developer here Choose CUSTOM The following steps are default or can choose from After setting: 19 Choose Installation: 20 Click: Click Next: 21 Here appears an error message about Windows Firewall, but without compromising, continue to press next: 22 Select "Perform a new ." And then continue next: Select Select All (or you can choose according to your needs): Enter a name in the Named Instance section: 23 Select Mixed Mode… and enter Password: Select Add Current User and then Next: 24 Continue to select Add Current User and click Next: 25 Select Add Current User and click Next: Installation confirmation Select Install to proceed with the installation: 26 During the self-installation process: Installation is complete, click Close to complete: 27 2.