MIS602: Data Modelling & Database Design - Practical Assignment

This assignment solution addresses a practical evaluation of database programming, focusing on data modeling and database design principles. The student's work includes creating a database and tables (EMPLOYEE, DEPARTMENT, SALARY), inserting records, and executing SQL queries. The solution demonstrates the use of DDL statements, data types, and key constraints. The analysis highlights the correct implementation of INSERT and SELECT statements, while also pointing out areas for improvement, such as query accuracy and the application of more complex SQL concepts like joins and subqueries. The document also provides suggestions for enhancing SQL understanding through practice and the use of naming conventions, case consistency, and the application of aggregate functions. Additionally, the solution references useful readings like 'Fundamentals of Database System' to develop a better understanding of SQL. The document demonstrates an understanding of database management system design and database systems.

Running head: DATA MODELLING & DATABASE DESIGN
DATA MODELLING & DATABASE DESIGN
Name of the Student
Name of the University
Author Note

1DATA MODELLING & DATABASE DESIGN
Table of Contents
Overall usage of SQL statements and commands...........................................................................2
Improve understanding of SQL statements.....................................................................................2
Some useful readings.......................................................................................................................3
References........................................................................................................................................4

2DATA MODELLING & DATABASE DESIGN
Overall usage of SQL statements and commands
Starting from the DDL statements, it has been implemented perfectly as per the
instructions. The data types, their sizes and key constraints are well defined. The insert statement
is also well structured as per the table columns (Rockoff, 2016). However, as the department_id
in the department table is parent table of the employees table. The data insertion in the
department table may have been done first. The usage of the SELECT statements have been done
on the required places. Task 9 and 22 has been implemented efficiently. Some of the Query
seems to be incorrect such as task 23. The overall usage of the SQL statements is average.
However, the concept needs to be clearer.
Improve understanding of SQL statements
The improvement in understanding the SQL statements can be achieved by the practice
itself. The practice means the different types of scenarios and different type of complex queries.
Some of the example of the complex queries which can make the concept of writing SQL clear
are the joins, sub queries, having, group by, alias, etc. can be helpful. Some of the areas are good
such as using WHERE, LIKE, UPDATE, etc. One of the most important aspects of the database
is the naming convention (Data, 2015). The naming convention follows TableName or
Table_Name, hencel; it helps in determine the difference between column name and table name
during performing any query. Also the consistency in the alphabet cases is not present. Some of
the names in the employee table are in uppercase and some in Sentence case. To avoid any kind
of case-sensitive query or code, storing data with case consistency is most important. Some
queries are missing group by statements or concept of max and min on a numeric value. Task 10
does not give the employee names with minimum and maximum salaries. It can be done with the

3DATA MODELLING & DATABASE DESIGN
help of sub query or UNION syntax. Task 17 is missing the ‘DESC’ syntax after ‘ORDER BY
Salary’ to show the list in descending order.
Some useful readings
SQL (structured query language) is used to facilitate storing and retrieving from a
database. As per its name, a single statement should always have to be structured (Din, 2014). To
develop the better understanding of SQL, one should start from the starting point. Practicing
ERD and conceptual schema along with multiple different case studies can help. ‘Fundamentals
of Database System’ is a great book to start learning database from basics. The book provides
certain types of case studies and business scenarios to develop entity relationship diagram,
relational schema and the actual databases (Elmasri & Navathe, 2017). Then comes the DML
(Data Manipulation Language) part to manipulate the data in a way that it produces some
analyzed result. Practicing different SQL such as MY SQL, SQL Server, Oracle, etc. are the best
platform to develop database. Apart from the books, the official websites of the platforms and
W3Schools are also provide learning of database with the help of SQKL

4DATA MODELLING & DATABASE DESIGN
References
Data, M. H. (2015). Database Design. Perancangan Basis Data) merupakan salah satu.
Din, A. I. (2014). Structured query language (SQL) A practical Introduction.
Elmasri, R., & Navathe, S. (2017). Fundamentals of database systems (Vol. 7). Pearson.
Rockoff, L. (2016). The language of SQL. Addison-Wesley Professional.