Choosing the Right Database Engine and Designing a Relational Database System, Cheat Sheet of Mathematics

Download Choosing the Right Database Engine and Designing a Relational Database System and more Cheat Sheet Mathematics in PDF only on Docsity! Unit 4: Database Design and Development Table of Contents LO1 Use an appropriate design tool to design a relational database system for a substantial POD eM... ccc cece cece cee cee cess cesecesecesecesesssesseecssecssessseseeesseesssesseesseeaeeaeesseeasecaeesasaeeceasangees 1 P1 Design a relational database system using appropriate design tools and techniques, containing at least four interrelated tables, with clear statements of user and system requirements. LO2 Develop a fully functional relational database system, based on an existing system P2 Develop the database system with evidence of user interface, output and data validations, and querying across multiple tables.. P3 Implement a query language into the relational database system..................0..000c008 19 Unit 4: Database Design and Development LO1 Use an appropriate design tool to design a relational database system for a substantial problem. P1 Design a relational database system using appropriate design tools and techniques, containing at least four interrelated tables, with clear statements of user and system requirements. Database use to storage the data and information that use in everything. With a database engine, it can perform some of basic tasks, which is help user to create, sort, change or serve the information on the database. Those data can get updated, expanded or deleted or added new information by using database engine. There many database engines available, from both free and paid. Check out some popular databases. 1. Oracle 18c: C ORACLE Database This database engine is the world’s most popular database. It provides businesses of all sizes with access to the world’s fastest, most scalable and reliable database technology for secure and cost-effective deployment of transactional and analytical workloads in the Cloud, on- premises and Hybrid Cloud configurations. 1|Page Unit 4: Database Design and Development Disadvantages: - Highly cost of the Enterprise version. - Even with performance tuning, Microsoft SQL Server can gobble resources. 4|Page Unit 4: Database Design and Development 3. MySQL: MySQL: MySQL is a most popular web-based database application. It’s free, it’s also frequently update with many features and securities. This database engine allows user to select a wide variety of storage engines that enable you to change the functionality of the tool and handle data from different table types. Advantages: - Import and export the databases. It shows relational graphs between the tables. - Entry-relationship model chart. - Easy to setup. - It’s available for free. - Provides best query performance with joins Disadvantages: - Spend lot of time and effort to get MySQL to do things that other systems do automatically. 5|Page Unit 4: Database Design and Development - Can be tough to use with large datasets. - Support is available for the free version, but you'll need to pay for it. 4. Microsoft Access: Microsoft Access has the look and feel of other Microsoft Office products, including its layout and navigational aspects. Microsoft Access is a database and, more specifically, a relational database, an information management tool that helps you store information for reference, reporting, and analysis. Microsoft Access helps you analyze large amounts of information, and manage related data more efficiently than Microsoft Excel or other spreadsheet applications. Advantages: - It integrate many tools. - Very easy to install and use. - Have a large amount of storage capacity. - Easy to import data. Disadvantages: - Lack of security. 6|Page Unit 4: Database Design and Development Time Product Date Product 1D Date Description Product Description, Month Category Marth Description Category Description Year Unit Price Week Created IWeak Description Stove 1D (FQ Product ID (FK)} Date (FE) Terns Sold Sale Amount Store Store ID Store Description Regian Region Name Created - Physical Data Model: Physical data models can be developed based on a Logical Data model. This model provides the blueprint for a physical manifestation, such as a relational database of the logical data model. Dat TIME DIM_pRODUCT DATE 1D; INTEGER PRODUCTID; INTEGER DATE_DESG: VARCHARG®) PROD _DESC: VARCHAR(50) MONTH ID! INTEGER CATEGORY ID: INTEGER MONTH DESC: VARCHAR(30}) | CATEGORY_DESC: VARCHAR(SI} ‘YEAR: INTEGER UNIT _PRICE: FLOAT ‘WEEK ID: INTEGER CREATED: DATE ‘WEEICDESC: VARCHAR(30) TTEMS_SOLD: INTEGER SALES_AMOUNT: FLOAT} DIM_STORE, STORE_ID: INTEGER 'STORE_BESC: VARCHAR(EO) REGION ID: INTEGER REGION NAME: VARCHAR(50)| CREATED: DATE There are few basic components of an entity relationship diagram: Entity: In ERD, there an object or a concept that called Entities, which are objects or concepts that can have data stored among them. Entities are normally named as noun such as product, 9|Page Unit 4: Database Design and Development customer, location, or promotion which based on the real-world item or concept that exist on its own. There 3 types of entities that frequently use in ERD. Entity Symbol Name Description Strong These shapes are independent from other entities, and Entiy entity are often called parent entities, since they will often have weak entities that depend on them. They will also have a primary key, distinguishing each occurrence of the entity. Weak entity Weak entities depend on some other entity type. They ‘Weak Entity don't have primary keys, and have no meaning in the diagram without their parent entity. Associative Associative entities relate the instances of several entity ‘associative Entity entity types. They also contain attributes specific to the 10|Page Unit 4: Database Design and Development Entity Symbol Name Description relationship between those entity instances. Attributes: Each of entity has its own attributes and characteristics that describe the entity. The set of all possible values of an attribute, such as integers from 0 to 100, is the attribute domain. For example, a person entity has the Date of Birth attribute. The relationships between those entities: Cardinality refers to the maximum number of times an instance in one entity can relate to instances of another entity. While ordinality is the minimum number of times an instance in one entity can be associated with an instance in the related entity. Cardinality and ordinality are shown by the styling of a line and its endpoint, according to the chosen notation style. re One ES Many —.—_ ++ One (and only one) =. Ot Zero or one SUE One or many 0K Zero or many 11|Page Unit 4: Database Design and Development 22 /*Show the prices*/ 23 Gicreate table [Price] ( 24 25 26 27 itemID int Primary Key Foreign Key References [Item](itemID), supplyPrice float NOT NULL, salePrice float NOT NULL ) /* Category list */ 30 ficreate table [Category] ( categoryID int Identity(1,1) Primary Key NOT NULL, categoryName nvarchar(255) unique NOT NULL, categoryDescription nvarchar(255), ) /*Show items stock that the shop have */ 37 Elcreate table [stock] ( 44 45 46 47 48 49 58 51 52 53 54 55 56 s7| 58 so] 60 61 itemID int Primary Key Foreign Key References [Item] (itemID), currentStock int NOT NULL, unitStock varchar, Limitsteck int ) /*Information about staff that work and take order */ Eicreate table [Staff] ( staffID int Identity(1,1) Primary Key, staffName nvarchar(255) NOT NULL, staffAddress nvarchar(255) NOT NULL, staffEmail varchar(255) ) /*Order Detail */ Elcreate table [Order] ( orderID int Identity(1,1) Primary Key, orderDetail nvarchar(255), orderPrice float NOT NULL, orderStaffID int Foreign Key References [Staff](staffID), orderDateTime Datetime, itemID int Foreign Key References [Item](itemID), ) 14|Page 4: Database Design and Development 62 | /*Customer Information */| 63 Gicreate table [Customer] ( 64 customerID int Identity(1,1) Primary Key, 65 customerNlame nvarchar(255) NOT NULL, 66 ccustomerPhone int check (len(customerPhone)-16) NOT NULL, 7 customerEmail varchar(255), 68 customerAddress nvarchar(255) NOT NULL 69 ) 78 71 | /* Billing information for the shop and customer */ 72 Gereate table [Billing] ( 73 billingID int Identity(1,1) Primary Key, 74] billingName nvarchar(255), 75 billingAddress nvarchar(255), rl billingPaymentType nvarchar(255), 7 ) 15|Page Unit 4: Database Design and Development Price Then | using SELECT to select all the table in the Database. % item supphPrice sletrice | Item F % ited 8 supplier Stock supplariome fenton % itemid amie ssupplierAddress * a] surrentstock supplierimst unitStock supelerthone nenOeerit matStock nembiome “ eategor/iD Order order Soe — v F eategona ordered us eatagoryhame wider categonDeeription cusomePhane fF corderStaHfiD seustomerEmail rderDateTine cumomertdices itemith corded) “Le Staff oi Billing staffhiome % bang asides: baingName staffs bitingAddress batingPayment Type cuustoment 16|Page Unit 4: Database Design and Development Bw no Ao hk wR = Next step, I’ll design a form for the database to import to. With Visual Studio, | created 2 forms for this database. In the first form, the blank space is the datagridview for the database to show when on the left side, there a category switch button. Those button will help user to choose what they want. For Example, when user press on the Order tab, the data will show in the datagridview. 19|Page Unit 4: Database Design and Development Benim Beas sen) tee ae a as — = Thensaclixes Name: ‘Sale Price: oo panes at — — Soke: (Er aR —_— Unit stDox Unt | | pres And when user press in Add or Edit, there will be a now window pop-up and ask them what user want to input. P3 Implement a query language into the relational database system. With INSERT, we can insert data into database and SELECT to show the database: 20|Page Unit 4: Database Design and Development CoN ou bu /*Supplier */ EIINSERT VALUES: EIINSERT VALUES, EJINSERT VALUES, Select /*Ttem EJINSERT VALUES, EJINSERT VALUES, EIINSERT VALUES: Select INTO dbo.Supplier (supplierName, supplierAddress, suppliertmail, supplierPhone) (‘Michel One','L Somewhere", 'moll@mail.com' ,@123456789) ; INTO dbo.Supplier (supplierName, supplierAddress, suppliertmail, supplierPhone) (‘Michel Two','L2 Somewhere", 'mt22(@mail. com" ,@223456789) ; INTO dbo.Supplier (supplierName, supplierAddress, supplierEmail, supplierPhone) (‘Michel Three" ,'123 Somewhere’ , 'wt3@mail.com' ,@323456789) ; * from [Supplier]; ~ INTO dbo.Item (itemName, itemPrice, itemDetail, itemDescription, category1D) (/PS4' 908, "P54", "Fun to Play", 2)5 INTO dbo.Item (itemName, itemPrice, itemetail, itemPescription, categoryID) ('PCL',400@,'PC', ‘Fun to Work", 1); INTO dbo. Item (itemName, itemPrice, itembetail, itemPescription, categoryID) (‘Smart Watch’ ,2000,'Smart Watch','Fun to Watch’, 4); © from Item; 21|Page Unit 4: Database Design and Development And then Delete and Update: FJUPDATE Customer 1 2 | set customerPhone = @523781513 3 where customerID = 4; 4 5 EIUPDATE Stock 6 | set currentStock = 13 7 (where itemID = 1; 8 9 | Delete from [Order] where orderID = 2; 18 il | Delete from Billing where billingID = 3; Result: It’s update the customer table where the customer ID = 4, it’s set to 0523781513 and currentStock to 13 where itemID = 1 in Stock. Then it’s delete from Order table the row with ID = 2 and the row in Billing table where ID table = 3. Advance Command in SQL: 23 SELECT [Order].orderID, Item.itemName, Price.salePrice 24 | From [Order] 25 | INNER JOIN [Item] ON [Order].ItemID = Item.itemID 26 | INNER JOIN [Price] ON [Order].orderPrice = Price.salePrice; EB Results pill Messages orderID itemName —salePrice 1 3 PC1 4000 2 4 Pcl 4000 3 5 PCI 4000 24|Page Unit 4: Database Design and Development Result: For each round Order table, INNER JOIN clause compares it with each row of the Item table to check if both of them satisfy the join condition. When the join condition is met, the INNER JOIN will return a new row which consists of columns in both Order, Item and Price tables. It’s show the orderID, ItemName, salePrice of those 3 tables. 28 [| SELECT COUNT(customerEmail) , Cus|tomerName 29 FROM Customer 3e GROUP BY customerName; 24 FA Results gill Messages {No column name) || CustomedName 1 1 : Billy 2 1 Thersa 3 4 \Wulan Result: With this command, it’s select how many customerEmail that a CustomerName has in the Customer table and group it’s by customerName. 25|Page Unit 4: Database Design and Development References: [4] http://www.sqlcourse.com/intro.html [2" February,2019 - 17:31 [2] https:/Awww.keycdn.com/blog/popular-databases [12" February, 2019 - 8:07] [3] https://searchdatamanagement.techtarget.com/definition/entity-relationship- diagram-ERD [12" February, 2019 - 10:07] [4] https://www.lucidchart.com/pages/ER-diagram-symbols-and-meaning?a=0 [14 February, 2019 - 21:34] [5] https:/Awww.w3schools.com/sql/default.asp [12" February, 2019 - 13:24] E-book: [6] Beginning Microsoft SQL Server 2012 Programming [7" February, 2019 - 0:24] [7] Introducing Microsoft SQL Server 2014 [7 February, 2019 - 0:45] 26|Page