Assignment 6 – SQL Concepts and Database Design
CIS 515 – Strategic Planning for Database Systems
Application of SQL Concepts and Database Design
In general database design plays an important role in the performance of a database. This explain why organization nowadays place a lot of emphasis in the database design process during the implementation of a new system. However, factors as database require needs to be consider during the initial DBLC as this will have a direct impact on the type of database. For instance, a transactional database emphasis accurate and consistent data as well as operational speed. Based on information provided from the finance industry, this paper addresses topics related to SQL concepts and database design from creating a data dictionary which comprising of data structure, datatype and format as well as data range and suggestion of primary and foreign key constraints.
| Entity Name | Attribute Name | Contents | Data Type | Pk or FK | |
|---|---|---|---|---|---|
| Employee | Emp_ID | Employee ID | Number | PK | |
| Emp_FN | Employee First Name | Varchar2 | |||
| Emp_LN | Employee Last Name | Varchar2 | |||
| Commission | Commission Rate | Number | |||
| Salary(Y) | Yearly Salary | Number | |||
| Dept_ID | Department ID | Number | FK1 | ||
| Job_ID | Job ID | Number | FK2 | ||
| Invoice | Inv_Number | Invoice Number | Number | PK | |
| Inv_Date | Invoice Date | Date | |||
| Emp_ID | Employee ID | Number | FK1 | ||
| Inv_Amount | Invoice Amount | Number | |||
| InvoiceLine | InvLineNumber | Invoice Line Number | Number | PK | |
| Inv_Number | Invoice Number | Number | FK1 | ||
| Product_ID | Product ID/ Number | Number | FK2 | ||
| Quantity | Quantity | Number | |||
| Product | Product_ID | Product ID/ Number | Number | PK | |
| Product_Descrip | Product Description | Varchar2 | |||
| Cost | Product Cost | Number | |||
| Department | Dept_ID | Department ID | Number | PK | |
| Dept_Descrip | Department Description | Varchar2 | |||
| Job | Job_ID | Job ID | Number | PK | |
| Job_Descrip | Job Description | Varchar2 | |||
| Customer | Cust_ID | Customer ID | Number | PK | |
| Cust_Name | Customer Full Name | Varchar2 | |||
| Cust_address | Customer Address | Number | |||
| Cust_Phone | Customer Phone No. | Number |
PK= Primary Key
- Creating a Data Dictionary that Document the Finance Industry and an ERM
FK = Foreign Key
Varchar2=Variable Character
Number = Regular Numeric data
Date= Date.
2.1 Using the DATEDIFF function to Construct Query Depicting Number of days between Invoices
SELECT EMPNUMBER, MONTH, (MAX (INVDATE) – MIN (INVDATE)) AS DIFF_DAYS FROM (SELECT A.EMPNUMBER, TO_CHAR (INVDATE,’MON’) AS MONTH, INVDATE FROM INVOICE A, EMPLOYEE B WHERE A.EMPNUMBER=B.EMPNUMBER GROUP BY A.EMPNUMBER, INVDATE) GROUP BY EMPNUMBER, MONTH ORDER BY EMPNUMBER, MONTH;
2.2 Query Showing Expected Payment Date for Invoice that Due within 30days
SQL> SELECT INVNUMBER, INVDATE+30 AS DUE_DATE, TRUNC (SYSDATE-INVDATE) AS DIFF_DAYS FROM INVOICE WHERE TRUNC (SYSDATE-INVDATE) <=30;
2.3 Query Showing Customers Distinct Area Codes
SQL> SELECT DISTINCT SUBSTR (CUSTOMERPHONE, 1, 3) FROM CUSTOMER;
3 Creating a Plan of the Necessary Activities that would be required to implement a valid Data base Design process.
The database design process which is also refers to as DBLC goes through six phases as:
Even though, the different databases categories are designed to run on high-performance servers as such possess a higher price tag. It’s important to do a careful needs analysis before you dive in and commit to a database solution. After performing an organization need analysis it is possible to discover that a desktop database is suitable for your business requirements of which without that an organization may consider to purchase an expensive server-based solution. Need analysis also makes it easy to uncover hidden requirements that necessitate the deployment of a scalable server-based database (Chapple, 2014).
- Requirement collection and analysis: Requirement collection is an important part of any project and project management. This step consist of researching what the project will deliver is crucial to its success.
- Conceptual database design: In this phase, an Entity-Relationship (ER) diagram or UML class diagram are drawn and forms an advanced database model of the particular application area. Within this phase, the various types of entities are identify and their relationship to each other and the kind of attributes each represents. The advantage ER diagram range from choosing an independent of DBMS, smooth communication between end-users and developers, clear conversion method from high-level model to relational model and the conceptual schema is a permanent description of the database requirements
- Choosing a DBMS: Database management systems comprise of two categories – desktop databases and server databases, however, the choice of category with depend on the number of user. For instance, desktop databases are oriented toward single-user applications and reside on standard personal computers. On the other hand, server databases contain mechanisms to ensure the reliability and consistency of data and are geared toward multi-user applications.
References
- Data model mapping: The result of the logical design phase is a set of relation schemas and ERM or class diagram forms the basis of this phase. However, creating relation schemas require a mechanical operation and there are also rules or how the ER model or class diagram is transferred to relation schemas. The relation schemas are the basis for table definitions and the primary keys and foreign keys are well define within this phase (OAMK, 2014).
- Physical database design: This phase have as objective the implementation of the database and determining DBMS for usage. Since, different DBMS’s have different names for data types and have different data types. Also, it is within this phase that the SQL clauses to create the database are written and indexes, as well the integrity constraints and the users’ access rights are defined. Finally the data to test the database is added in and application programs are designed. Although a series of activities takes place in this step, the implementation of the programs can only start when the database is created and data loaded into the system (OAMK, 2014).
- Database system implementation: In this phase, the hardware, DBMS software, and application programs are installed, and the database design is implemented. It is at the initial stages of the implementation phase that, the system enters into a cycle of coding, testing, and debugging until it is ready to be delivered. At this phase, the actual database is created, and the system is customization through the creation of tables, views, and users authorizations. Finally loading of the database contents which is the last aspect might be done interactively or in batch mode, using different methods and devices such as Customized user programs, database interface programs, and conversion programs that import the data from a different file structure, using batch programs and database utility (Coronel, 2013).
Byron, B. (2011), The database design process;
http://www.ibiblio.org/faint/finosaur/db/step6.html
Chapple, M. (2014), Choosing a Database for Your Organization;
http://databases.about.com/od/administration/a/choosing_a_db.htm
Coronel, C. (2013), Morris, S. (2013), Rob, P. (2013), Database Systems design, implementation and management 10th edition
Haughey, D. (2014), Requirements Gathering 101;
http://www.projectsmart.co.uk/requirements-gathering.php
OAMK (2014), Main Phases of Database Design;
http://www2.amk.fi/digma.fi/www.amk.fi/opintojaksot/0303011/1146161367915/1146161680673/1146161874577/1146161968355.html