All this computer hacking is making me thirsty

Database design is an integral part of the overall development of databases. Without a properly planned database, recreation could be necessary. By designing a proper model, time is saved in the future. This occurs because a database designer will plan for many problems that could potentially occur in the future. Furthermore, unnecessary tables will be left out of the overall design. As we have learned in this class, there are many steps that are necessary in the creation of a database. Without proper knowledge, mistakes will be more than likely to occur. User requirements are targeted, hardware and software requirements are noted, and overall functionality improves. By understanding what will be in the database and creating a proper diagram allows for an efficient database. Included in the overall design are important aspects such as security, testing, storage, performance, and backup. Without these critical features/processes, many databases would be rendered useless and unimportant. By focusing on database design, a company can run efficiently without receiving many complaints and errors.

PL/SQL, or Procedural SQL, allows for procedural or programming code and SQL statements with databases. Basic SQL has its limitations and PL/SQL supports many useful functions including conditional statements, loops, and error trapping. Many of these functions are available in programming languages such as Java. Users can create:

Anonymous blocks – a set of statements that are usually run once.
DECLARE variable:= value;
BEGIN statements;
END;

Triggers – execute code when a specific action occurs
CREATE OR REPLACE TRIGGER name
AFTER INSERT OR UPDATE OF column ON table
BEGIN commands;
END;

Stored Procedures – named collection of statements
CREATE OR REPLACE PROCEDURE name
AS BEGIN commands;
END;

Cursors – Allows you to return more than one value without generating an error. Holds data returned from a SQL query in the memory of the DBMS server. Inside of a DECLARE section use the following code:
CURSOR name IS select-query;
Use OPEN, FETCH, or CLOSE after declaring the cursor.
More detail on pg 357-358 of the book.

Functions – allows you to return a value from a named collection of statements
CREATE FUNCTION name
BEGIN commands;
RETURN (value or expression);
END;

Use the line DBMS_OUTPUT.PUT_LINE(); to produce a specific message to the server output.

To create a loop, use to following code between the BEGIN and END statements:
WHILE condition
LOOP statements;
END LOOP;

An Inner Join returns only rows that match the given criteria whereas an Outer Join returns the rows with matching values and those that don’t have matching values.

To perform an inner join, use one of the following SQL statements:

SELECT * FROM table1, table2 WHERE expression
This is the old style that returns matching values from both tables that meet the expression.

SELECT * FROM table1 NATURAL JOIN table2
This is the natural join that returns matching values from both tables that have the same column names and data types.

SELECT * FROM table1 JOIN table2 USING column
This is the “join using” style that returns matching values from both tables that have the same column name.

SELECT * FROM table1 JOIN table2 ON expression
This is the “join on” style that returns matching values from both tables that meet the expression. This is the new-style for an inner join.

To perform an outer join, use the following SQL statement:

SELECT * FROM table1 [LEFT/RIGHT/ FULL] OUTER JOIN table2 ON expression

A left outer join returns all matching values between the two tables and those from table1 that do not have matching values. A right outer join returns all matching values between the two tables and those from table2 that do not have matching values. A full outer join returns all matching values between the two tables and those from both tables that do not have matching values. Do not include the brackets and choose only left, right, or full.

SELECT * FROM table WHERE clause – grabs all information from the specified table based on the clause that is defined. For example if it the table contained students that hadn’t paid their tuition, a query can generate a list of people to remind (SELECT * FROM student WHERE payment tuition;)

INSERT into table VALUES() – adds data to the specified row. Use the term NULL to leave a field blank, separate each value using commas, and place any character value in quotes.

CREATE VIEW name AS SELECT… – Saves a query that can be recalled quickly and easily any time in the future.

UPDATE table SET value = new value WHERE clause – Change a specific value in a table when you want to make updates.

CREATE SEQUENCE name [START WITH number] [INCREMENT BY number] [CACHE | NO CACHE] – A sequence automatically creates the next number in a defined series. Using the INSERT command, use the sequence_name.NEXTVAL to allow a unique value. Use .CURRVAL after using .NEXTVAL to define the same number (i.e. a foreign key). Anything in brackets is an optional value to be defined.

Remember that all SQL lines should be ended by a semicolon (;).

Converting a conceptual model into a set of relations is the next largest step before entering data. It requires the user to know the fields that they want to include in the table as well as the relationships. To create the relations, draw a box and write the name of the entity at the top. Draw a line below it to separate the entity name from the fields. Below the line, write each field that is included in the relation. Connect each relation in the same style as an ER Diagram, identifying which specific fields are related. Identify any primary and foreign keys within each relation.

However, when using Oracle there is more detail to create the tables. By creating an internal model, you are creating the tables from the database. The difficult way is to write out all the tables using SQL, however Oracle provides an easier way using a GUI that will write the SQL for you. After connecting to your database, expand the navigation tree on the right side and right click on “Tables.” Select “Create Table” and press the “advanced” button on the new box. By working in the advanced section, you can add each field using the green plus, field size and type can be declared, primary and foreign keys can be identified, and integrity can be set. Oracle is a great program that can help to build relations for real-time use.

Since I have not used SQL Developer Data Modeling, I have not run into any problems. Howver, the more experience that I will have with it, the better I will understand it. After drawing many ER diagrams, I will begin to use the SQL Developer to create cleaner and better organized diagrams. The transition will have a learning curve to it but I am sure that I will improve over time. Internet resources and asking classmates will improve my understanding of the program.

A Guide to Data Modeling

Entity – something you store in a database

Attributes make up the entity – property or characteristic

Aka columns and fields

Relationships – object that associates entities

Links related entities

Student take Course

Customer purchases Item

Aka an association

Can have multivalued attributes

Person has multiple college degrees, personal trainers, cars, etc.

ER Modeling

Entity – noun

Relationship – verbs

Participation

Circle = optional

Dashed line = one

Crows foot = many

Cardinality

M-n = many to many

1-1 = one to one

1 – m = one to many

I am a computer information systems major from Bentley University in Waltham, MA, USA. I have designed websites in my personal time and helped with the management and design of websites during a summer internship. My experience with databases has been with Microsoft Acess and little SQL. At Bentley, I work in the computer resource center, repairing student’s laptops. I have also worked as a MIS intern, assisting in networking and deployment of computers with the company. I love technology and learning about the upcoming developments that companies have to offer.