SQL Concepts and Database Design

21 Aug No Comments

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




Click following link to download this document

CIS 515 Assignment 6 – SQL Concepts and Database Design.docx