Question 1
- The database management system is a collection of various types of data. In the database management systems. The end users is not needed to the write the processes.
- The file system is a collection of various types of data. In this type of system, the end user has to write the processes in order to manage the databases.
- The database management systems give a view of the data that is abstract and that conceal the details of the data.
- The file system gives the detail of the representation of data and the data storage.
- The database management systems give a mechanism of recovery of crash that is the database management systems protect the end user from the failure of the system.
- The file system do not have a mechanism of crash that is by any chance the system crashes while entering some data, then the content that are within the file will be lost.
- The database management system gives a good mechanism of protection.
- It is very hard to protect a file that is under the file system.
- The database management system consists of wide range of techniques that are sophisticated in order to retrieve and store data.
- The file system does not have the efficiency to retrieve and store data
- The database management systems take care of the access of data that are concurrent using some form of locking techniques.
- In the file system, the access that is concurrent has many issues like the redirection of the file while other deleting some of the information or upgrading some information.
Question 2
The features of the hierarchical model of database are:
- Many-to-many relationships: the hierarchical model of database supports only one-to-many relationships.
- Hierarchy of data: the data of the database is represented in a tree like structure that is hierarchical.
- Problem in deletion: If a parent is deleted then the child of the tree will be deleted automatically.
The hierarchical model of database is utilized for storing the geographical information and the systems of file. They are also used in telecommunications and banking. The characteristics of the network model database are the following:
- The network model of database is better than the hierarchical model of database
- In this model, many parents have many children.
- In the model of network, many children of the tree can have many parents.
- The entities that are present in the database are represented as a network that is connected with each other.
- The network model supports relationships that are many-to-many.
The network model of database is used in the development of the software where it is used in the backend of the software. The characteristics of the relational model of data are the following:
- The entire data is represented conceptually as an arrangement of data that is orderly.
- All the value of the relational model of database are vector in nature.
- In this model, at any provided position of row or column in the relation there is always only one value.
The application of the relational model of database is to give a method that is declarative for specification of data and the queries in the database management system software.
Question 3:
The strength and the weakness of the top-down and bottom up approach are:
The strengths are:
| Bottom-up approach | Top down Approach |
| Business and user awareness of the product | The organization realizes a utilization of the resources that are focused from the managed application that is individual. |
| In this, the user can replace many procedures that are manual with the early automation. | The implementation that is first becomes a showcase for the solution of management of identity |
| The user can implement management of password for many users. | When the stages are completed for the application that is managed, the implementation can be deeper. |
The weaknesses of the both approaches are:
| Bottom-up approach | Top-down approach |
| The structure of the organization that the users establish can change in the later phase | The solution gives a coverage that is limited in the first stage. |
| The strategy is driven by the architecture that already exists. | The cost of implementation is very high. |
The approach that will be used in this task is the Top-down approach.
Task 2: conceptual modelling
Question 1:
Sale Online is an international company thus there is a need to develop effective RDBMS that will help in organizing the major components of the business. Thus while developing the database for the saleonline organization the key entities and attributes are described below in the ascending order:
Customers (Cust_id(pk), cust_name, cust_address, product_id (fk))
Employees (empid (pk), Ename, dept_emp, salary,emp_address)
Orders (order_id (pk), cust_id(pk), product_id (fk), order details, empid(fk))
Products (product_id (pk), product_name, description, price)
Question 2
Task 3: logical modelling
Quetsion1:
Normalization is the concept that is in database for the purpose of referring to the technique that helps in normalizing the attributes. This helps in modifying the database structure by offering better and simpler way of breaking the components. With the help of normalization it becomes easy to design the database and ensures that the chances of data redundancy within the database has reduced to great extent. The key benefits that are offered with the use of normalization is that it offers greater control over the database and helps in organizing the data sets in a simpler form. With the help of normalization it becomes easy to manage the integrity and enhances the design of the database. The data consistency within the database also increases that increases the efficiency within the system. With the use of normalization in database it becomes easy to maintain the flexibility. Thus it can be stated that with the help of normalization the data can be broken down into simple form and the data can be organized in an effective way.
Normalization of the database for SaleOnline
Customers (Cust_id(pk), cust_name, cust_address, product_id (fk))
- The above table is said to be on UNF as it maintains a relation with all the entities and contains a primary key.
- The table contains a primary key that is used to uniquely identifying the table and thus it is said to be in 1NF.
- The primary key in the table has the ability to act like a single candidate key and no other attribute possess any unique value, thus it is 2NF relation.
- The table does not have any partial transitive dependency and thus it is said to be in 3NF.
Employees (empid (pk), Ename, dept_emp, salary,emp_address)
- The employee table is in UNF because identification is done with the help of unique attributes.
- Employee is in 1NF relation because it contains one primary key for identification purpose.
- The employee table does not have any other attribute that is used for unique identification and thus it is stated to be in 2NF.
- The employee table is said to be in 3NF as it does not have any transitive partial dependency with other attributes.
Orders (order_id (pk), cust_id(pk), product_id (fk), order details, empid(fk))
- Orders is in UNF relation as it contains only one unique value that is used for storing the unique data sets.
- The table is said to be in 1NF relation as it contains only one primary key
- The relation is said to have 2NF as it have one unique attribute
- The table is in 3NF as it does not contains any partial transitive dependency.
Products (product_id (pk), product_name, description, price)
- Product table is in UNF as it contains single primary key attribute in the table.
- The table is 1NF as product_id is used for uniquely identifying the products in the database.
- The products table maintains a 2NF relation as it contains a single attribute as unique id.
- The table is stated to be in 3NF as it does not maintain any transitive dependency.
Question 2
Customers (Cust_id (pk), cust_name, cust_address, product_id (fk), purchaseid (fk))
Department (dept_id (pk), empid (fk), name, Numberofpeople)
Employees (empid (pk), Ename, dept_emp, salary, emp_address)
Orders (order_id (pk), product_id (fk), order details, empid(fk))
Purchase (PurchaseID (PK), order_id(fk), purchase_det, pur_date, purchasemethod)
Products (product_id (pk), product_name, description, price)
Question 3:
Task 4: Physical modelling
Question 1:
CREATE TABLE `customers` (
`Cust_id` int(30) NOT NULL,
`cust_name` varchar(30) NOT NULL,
`cust_address` varchar(30) NOT NULL,
`product_id` int(30) NOT NULL,
`purchaseid` int(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
—
— Dumping data for table `customers`
—
INSERT INTO `customers` (`Cust_id`, `cust_name`, `cust_address`, `product_id`, `purchaseid`) VALUES
(100001, ‘Riza Sussane’, ‘Mellbourne’, 10001, 101993030),
(1092023, ‘Andrew Sussaine’, ’10,A Hill road’, 10001, 188390330);
— ——————————————————–
—
— Table structure for table `department`
—
CREATE TABLE `department` (
`dept_id` int(30) NOT NULL,
`empid` int(30) NOT NULL,
`name` varchar(30) NOT NULL,
`Numberofpeople` int(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
—
— Dumping data for table `department`
—
INSERT INTO `department` (`dept_id`, `empid`, `name`, `Numberofpeople`) VALUES
(1, 102, ‘Manager’, 5),
(9, 101, ‘Team leader’, 5);
— ——————————————————–
—
— Table structure for table `employees`
—
CREATE TABLE `employees` (
`empid` int(30) NOT NULL,
`Ename` varchar(30) NOT NULL,
`dept_emp` varchar(30) NOT NULL,
`salary` int(30) NOT NULL,
`emp_address` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
—
— Dumping data for table `employees`
—
INSERT INTO `employees` (`empid`, `Ename`, `dept_emp`, `salary`, `emp_address`) VALUES
(101, ‘Arish Bell’, ‘Manager’, 20000, ‘Mellbourne’),
(102, ‘Crish Sam’, ‘Assistant developer’, 30000, ‘Chicago’);
— ——————————————————–
—
— Table structure for table `orders`
—
CREATE TABLE `orders` (
`Order_id` int(30) NOT NULL,
`product_id` int(30) NOT NULL,
`order details` varchar(30) NOT NULL,
`empid` int(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
—
— Dumping data for table `orders`
—
INSERT INTO `orders` (`Order_id`, `product_id`, `order details`, `empid`) VALUES
(92092, 10982, ‘5’, 102),
(1901918, 10001, ‘8’, 101);
— ——————————————————–
—
— Table structure for table `products`
—
CREATE TABLE `products` (
`product_id` int(30) NOT NULL,
`product_name` varchar(30) NOT NULL,
`description` varchar(30) NOT NULL,
`price` int(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
—
— Dumping data for table `products`
—
INSERT INTO `products` (`product_id`, `product_name`, `description`, `price`) VALUES
(10001, ‘Nestle Chocolate’, ‘chocolate bar’, 250),
(10982, ‘Milk ‘, ‘Condensed Milk’, 300);
— ——————————————————–
—
— Table structure for table `purchase`
—
CREATE TABLE `purchase` (
`PurchaseID` int(30) NOT NULL,
`order_id` int(30) NOT NULL,
`pur_date` date NOT NULL,
`purchasemethod` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
—
— Dumping data for table `purchase`
—
INSERT INTO `purchase` (`PurchaseID`, `order_id`, `pur_date`, `purchasemethod`) VALUES
(101993030, 1901918, ‘2019-07-16’, ‘Card payment’),
(188390330, 92092, ‘2019-07-24’, ‘Cash payment’);
—
— Indexes for dumped tables
—
—
— Indexes for table `customers`
—
ALTER TABLE `customers`
ADD PRIMARY KEY (`Cust_id`),
ADD KEY `product_id` (`product_id`),
ADD KEY `purchaseid` (`purchaseid`);
—
— Indexes for table `department`
—
ALTER TABLE `department`
ADD PRIMARY KEY (`dept_id`),
ADD KEY `empid` (`empid`);
—
— Indexes for table `employees`
—
ALTER TABLE `employees`
ADD PRIMARY KEY (`empid`);
—
— Indexes for table `orders`
—
ALTER TABLE `orders`
ADD PRIMARY KEY (`Order_id`),
ADD KEY `product_id` (`product_id`),
ADD KEY `empid` (`empid`);
—
— Indexes for table `products`
—
ALTER TABLE `products`
ADD PRIMARY KEY (`product_id`);
—
— Indexes for table `purchase`
—
ALTER TABLE `purchase`
ADD PRIMARY KEY (`PurchaseID`),
ADD KEY `order_id` (`order_id`);
—
— Constraints for dumped tables
—
—
— Constraints for table `customers`
—
ALTER TABLE `customers`
ADD CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`),
ADD CONSTRAINT `customers_ibfk_2` FOREIGN KEY (`purchaseid`) REFERENCES `purchase` (`PurchaseID`);
—
— Constraints for table `department`
—
ALTER TABLE `department`
ADD CONSTRAINT `department_ibfk_1` FOREIGN KEY (`empid`) REFERENCES `employees` (`empid`);
—
— Constraints for table `orders`
—
ALTER TABLE `orders`
ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`),
ADD CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`empid`) REFERENCES `employees` (`empid`);
—
— Constraints for table `purchase`
—
ALTER TABLE `purchase`
ADD CONSTRAINT `purchase_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`Order_id`);
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Question 2:
INSERT INTO `employees` (`empid`, `Ename`, `dept_emp`, `salary`, `emp_address`) VALUES (‘101’, ‘Arish Bell’, ‘Manager’, ‘20000’, ‘Mellbourne’), (‘102’, ‘Crish Sam’, ‘Assistant developer’, ‘30000’, ‘Chicago’);
INSERT INTO `products` (`product_id`, `product_name`, `description`, `price`) VALUES (‘10001’, ‘Nestle Chocolate’, ‘chocolate bar’, ‘250’), (‘10982’, ‘Milk ‘, ‘Condensed Milk’, ‘300’);
INSERT INTO `orders` (`Order_id`, `product_id`, `order details`, `empid`) VALUES (‘092092’, ‘10982’, ‘5’, ‘102’), (‘1901918’, ‘10001’, ‘8’, ‘101’);
INSERT INTO `purchase` (`PurchaseID`, `order_id`, `pur_date`, `purchasemethod`) VALUES (‘101993030’, ‘1901918’, ‘2019-07-16’, ‘Card payment’), (‘188390330’, ‘92092’, ‘2019-07-24’, ‘Cash payment’);
INSERT INTO `department` (`dept_id`, `empid`, `name`, `Numberofpeople`) VALUES (‘001’, ‘102’, ‘Manager’, ‘5’), (‘009’, ‘101’, ‘Team leader’, ‘5’);
INSERT INTO `customers` (`Cust_id`, `cust_name`, `cust_address`, `product_id`, `purchaseid`) VALUES (‘100001’, ‘Riza Sussane’, ‘Mellbourne’, ‘10001’, ‘101993030’), (‘1092023’, ‘Andrew Sussaine’, ’10,A Hill road’, ‘10001’, ‘188390330’);
Bibliography
Battaglia, P.W., Hamrick, J.B., Bapst, V., Sanchez-Gonzalez, A., Zambaldi, V., Malinowski, M., Tacchetti, A., Raposo, D., Santoro, A., Faulkner, R. and Gulcehre, C., 2018. Relational inductive biases, deep learning, and graph networks. arXiv preprint arXiv:1806.01261.
Colombelli, A., Paolucci, E. and Ughetto, E., 2019. Hierarchical and relational governance and the life cycle of entrepreneurial ecosystems. Small Business Economics, 52(2), pp.505-521.
Floris, F., van Agthoven, M., Chiron, L., Soulby, A.J., Wootton, C.A., Lam, Y.P., Barrow, M.P., Delsuc, M.A. and O’Connor, P.B., 2016. 2D FT-ICR MS of calmodulin: a top-down and bottom-up approach. Journal of the American Society for Mass Spectrometry, 27(9), pp.1531-1538.
Islam, M.A., Rahman, M.M., Islam, A.T., Siddiquee, M.Z.H. and Muntaheen, A.S.M., 2017. Architecture of DBMS as Integrated Cloud Service and Its Advantages & Disadvantages. American Journal of Operations Management and Information Systems, 2(1), pp.37-41.
Kao, C., 2015. Efficiency measurement for hierarchical network systems. Omega, 51, pp.121-127.
Lee, C., Sim, D., Hwang, J. and Cho, S., 2015. F2FS: A new file system for flash storage. In 13th {USENIX} Conference on File and Storage Technologies ({FAST} 15) (pp. 273-286).
Ordonez, C., Cabrera, W. and Gurram, A., 2017. Comparing columnar, row and array DBMSs to process recursive queries on graphs. Information Systems, 63, pp.66-79.
Thiam, A., Xu, D., Miao, H. and Niu, Z., 2018, May. Comparison of Query Languages for Semantic Database Models. In Proceedings of the 2018 International Conference on Computing and Data Engineering (pp. 11-14). ACM.
Vaidya, M. and Deshpande, S., 2016. Critical study of performance parameters on distributed file systems using MapReduce. Procedia Computer Science, 78, pp.224-232.
Wilson-Strydom, M., 2016. A capabilities list for equitable transitions to university: A top-down and bottom-up approach. Journal of Human Development and Capabilities, 17(2), pp.145-160.
