OASIS Open Office Specification
draw a diagram - ommag.info
Fifth, give each diamond and square any attributes it may have a person has a name, an invoice has a numberbut some relationships have none a parent just owns a child. Sixth, everything on your page that has attributes is now a table, whenever two entities have a relationship where the relationship has no attributes, there is merely a foreign key between the tables. Seventh, in general you want to make tables not repeat data.
So, if a customer has a name and several addresses, you can see that for every address of a customer, there will be repeated the customer's first name, last name, etc. So, record Name in one table, and put all his addresses in another.
Eighth, each row record should be unique from every other one; Mr. Freedman suggests a 'auto-increment number' primary key, where a new, unique number is generated for each new inserted row. Ninth, a key is any way to uniquely identify a row in a table This concept is important because when the tables of a relational database are designed, these relationships must be defined because they determine which columns are or are not primary or foreign keys.
You may have heard of an Entity-Relationship Diagram, which is a graphical view of tables in a database schema, with lines connecting related columns across tables. See the sample diagram at the end of this section or some of the sites below in regard to this topic, as there are many different ways of drawing E-R diagrams.
But first, let's look at each kind of relationship A One-to-one relationship means that you have a primary key column that is related to a foreign key column, and that for every primary key value, there is one foreign key value.
Specifically, each employee in the EmployeeAddressTable has statistics one row of data in the EmployeeStatisticsTable.
Even though this is a contrived example, this is a "" relationship. Also notice the "has" in bold The other two kinds of relationships may or may not use logical primary key and foreign key constraints The first of these is the one-to-many relationship "1-M".
This means that for every column value in one table, there is one or more related values in another table. Key constraints may be added to the design, or possibly just the use of some sort of identifier column may be used to establish the relationship. An example would be that for every OwnerID in the AntiqueOwners table, there are one or more zero is permissible too Items bought in the Antiques table verb: Finally, the many-to-many relationship "M-M" does not involve keys generally, and usually involves identifying columns.
The unusual occurrence of a "M-M" means that one column in one table is related to another column in another table, and for every value of one of these two columns, there are one or more related values in the corresponding column in the other table and vice-versaor more a common possibility, two tables have a 1-M relationship to each other two relationships, one 1-M going each way. A [bad] example of the more common situation would be if you had a job assignment database, where one table held one row for each employee and a job assignment, and another table held one row for each job with one of the assigned employees.
Here, you would have multiple rows for each employee in the first table, one for each job assignment, and multiple rows for each job in the second table, one for each employee assigned to the project. These tables have a M-M: This is the tip of the iceberg on this topic What are some important nonstandard SQL features extremely common question?
In addition to the above listed aggregate functions, some DBMS's allow more functions to be used in Select lists, except that these functions some character functions allow multiple-row results are to be used with an individual value not groupson single-row queries.
The functions are to be used only on appropriate data types, also. Here are some Mathematical Functions: If Y is omitted, X is rounded to the nearest integer. Character Functions Returns the leftmost X characters of the string. Syntax Summary--For Advanced Users Only Here are the general forms of the statements discussed in this tutorial, plus some extra important ones explanations given.
Also, these physical specifications are used with the Create Table statement, when a table is first created. In addition, only one option can be performed per Alter Table statement --either add, drop, OR modify in a single statement.
Some software uses automatic committing on systems that use the transaction features, so the Rollback command may not work. Show each Antiques order and the last and first names of the person who ordered the item. Show the annual budget for Benefits from the EmployeeStatisticsTable.
Using the IN Operator, show the names of the owners of Chairs. Show the names of all Antiques Owners who have do not have an order placed. Show the names of those who have placed Antique orders, with no duplicates Hint: We can say that someone has wisdom when they can combine their knowledge and experience to produce a deeper understanding of a topic.
It often takes many years to develop wisdom on a particular topic, and requires patience.
How to Draw a Database Model Diagram
Examples of Data Almost all software programs require data to do anything useful. For example, if you are editing a document in a word processor such as Microsoft Word, the document you are working on is the data. The word-processing software can manipulate the data: Some other examples of data are: In some cases, such as with an e-book, you may only have the ability to read the data. Databases The goal of many information systems is to transform data into information in order to generate knowledge that can be used for decision making.
In order to do this, the system must be able to take data, put the data into context, and provide tools for aggregation and analysis. A database is designed for just such a purpose.
A database is an organized collection of related information. All information in a database should be related as well; separate databases should be created to manage unrelated information. For example, a database that contains information about students should not also hold information about company stock prices.
For the purposes of this text, we will only consider digital databases. The most popular form of database today is the relational database. Each table has a set of fields, which define the nature of the data stored in the table.
A record is one instance of a set of fields in a table. To visualize this, think of the records as the rows of the table and the fields as the columns of the table. In the example below, we have a table of student information, with each row representing a student and each column representing one piece of information about the student.
Rows and columns in a table In a relational database, all the tables are related by one or more fields, so that it is possible to connect all the tables in the database through the field s they have in common. For each table, one of the fields is identified as a primary key.
This key is the unique identifier for each record in the table. After interviewing several people, the design team learns that the goal of implementing the system is to give better insight into how the university funds clubs.
Chapter 4: Data and Databases – Information Systems for Business and Beyond
This will be accomplished by tracking how many members each club has and how active the clubs are. From this, the team decides that the system must keep track of the clubs, their members, and their events. Using this information, the design team determines that the following tables need to be created: Now that the design team has determined which tables to create, they need to define the specific information that each table will hold.
For example, Club Name would be one of the fields in the Clubs table. First Name and Last Name would be fields in the Students table. Finally, since this will be a relational database, every table should have a field in common with at least one other table in other words: This key is a unique identifier for each record in the table. However, it is more than likely that some students will share a last name like Rodriguez, Smith, or Leeso a different field should be selected.
However, a primary key cannot change, so this would mean that if students changed their e-mail address we would have to remove them from the database and then re-insert them — not an attractive proposition.
Our solution is to create a value for each student — a user ID — that will act as a primary key. We will also do this for each of the student clubs. This solution is quite common and is the reason you have so many user IDs!
You can see the final database design in the figure below: Student Clubs database diagram With this design, not only do we have a way to organize all of the information we need to meet the requirements, but we have also successfully related all the tables together.
Normalization When designing a database, one important concept to understand is normalization. In simple terms, to normalize a database means to design it in a way that: In the Student Clubs database design, the design team worked to achieve these objectives. For example, to track memberships, a simple solution might have been to create a Members field in the Clubs table and then just list the names of all of the members there.
However, this design would mean that if a student joined two clubs, then his or her information would have to be entered a second time.
Instead, the designers solved this problem by using two tables: In this design, when a student joins their first club, we first must add the student to the Students table, where their first name, last name, e-mail address, and birth year are entered.
This addition to the Students table will generate a student ID. Now we will add a new entry to denote that the student is a member of a specific club. This is accomplished by adding a record with the student ID and the club ID in the Memberships table. The design of the Student Clubs database also makes it simple to change the design without major modifications to the existing structure. For example, if the design team were asked to add functionality to the system to track faculty advisors to the clubs, we could easily accomplish this by adding a Faculty Advisors table similar to the Students table and then adding a new field to the Clubs table to hold the Faculty Advisor ID.
Data Types When defining the fields in a database table, we must give each field a data type. For example, the field Birth Year is a year, so it will be a number, while First Name will be text. Most modern databases allow for several different data types to be stored.
Some of the more common data types are listed here: The database designer can identify the maximum length of the text. There are usually a few different number types that can be selected, depending on how large the largest number will be.
First, a data type tells the database what functions can be performed with the data. For example, if we wish to perform mathematical functions with one of the fields, we must be sure to tell the database that the field is a number data type.ER Diagram - Part 2 ( Example )
So if we have, say, a field storing birth year, we can subtract the number stored in that field from the current year to get age. The second important reason to define data type is so that the proper amount of storage space is allocated for our data. For example, if the First Name field is defined as a text 50 data type, this means fifty characters are allocated for each first name we want to store. However, even if the first name is only five characters long, fifty characters bytes will be allocated.
It may be prudent to reduce the size of the field so we do not waste storage space. The Difference between a Database and a Spreadsheet Many times, when introducing the concept of databases to students, they quickly decide that a database is pretty much the same as a spreadsheet. After all, a spreadsheet stores data in an organized fashion, using rows and columns, and looks very similar to a database table.
This misunderstanding extends beyond the classroom: To be fair, for simple uses, a spreadsheet can substitute for a database quite well. If a simple listing of rows and columns a single table is all that is needed, then creating a database is probably overkill. In our Student Clubs example, if we only needed to track a listing of clubs, the number of members, and the contact information for the president, we could get away with a single spreadsheet. However, the need to include a listing of events and the names of members would be problematic if tracked with a spreadsheet.
A database allows data from several entities such as students, clubs, memberships, and events to all be related together into one whole. Though not good for replacing databases, spreadsheets can be ideal tools for analyzing the data stored in a database.
A spreadsheet package can be connected to a specific table or query in a database and used to create charts or perform analysis on that data. Structured Query Language Once you have a database designed and loaded with data, how will you do something useful with it? Almost all applications that work with databases such as database management systems, discussed below make use of SQL as a way to analyze and manipulate relational data. As its name implies, SQL is a language that can be used to work with a relational database.
From a simple request for data to a complex update operation, SQL is a mainstay of programmers and database administrators. To give you a taste of what SQL might look like, here are a couple of examples using our Student Clubs database. The following query will retrieve a list of the first and last names of the club presidents: President" The following query will create a list of the number of students in each club, listing the club name and then the number of members: Club ID" An in-depth description of how SQL works is beyond the scope of this introductory text, but these examples should give you an idea of the power of using SQL to manipulate relational data.
Many database packages, such as Microsoft Access, allow you to visually create the query you want to construct and then generate the SQL query for you.
Other Types of Databases The relational database model is the most used database model today. However, many other database models exist that provide different strengths than the relational model.
NoSQL arose from the need to solve the problem of large-scale databases spread over several servers or even across the world. For a relational database to work properly, it is important that only one person be able to manipulate a piece of data at a time, a concept known as record-locking. A NoSQL database can work with data in a looser way, allowing for a more unstructured environment, communicating changes to the data over time to all the servers that are part of the database.
Database Management Systems Screen shot of the Open Office database management system To the computer, a database looks like one or more files. In order for the data in the database to be read, changed, added, or removed, a software program must access it. Many software applications have this ability: But what about applications to create or manage a database?
That is the purpose of a category of software applications called database management systems DBMS. DBMS packages generally provide an interface to view and change the design of the database, create queries, and develop reports. Most of these packages are designed to work with a specific type of database, but generally are compatible with a wide range of databases.
For example, Apache OpenOffice. Both Access and Base have the ability to read and write to other database formats as well. Microsoft Access and Open Office Base are examples of personal database-management systems. These systems are primarily used to develop and analyze single-user databases. These databases are not meant to be shared across a network or the Internet, but are instead installed on a particular device and work with a single user at a time.
Enterprise Databases A database that can only be used by a single user at a time is not going to meet the needs of most organizations. As computers have become networked and are now joined worldwide via the Internet, a class of database has emerged that can be accessed by two, ten, or even a million people. These databases are sometimes installed on a single computer to be accessed by a group of people at a single location.
Other times, they are installed over several servers worldwide, meant to be accessed by millions. These relational enterprise database packages are built and supported by companies such as Oracle, Microsoft, and IBM.
The open-source MySQL is also an enterprise database. As stated earlier, the relational database model does not scale well. The term scale here refers to a database getting larger and larger, being distributed on a larger number of computers connected via a network. Some companies are looking to provide large-scale database solutions by moving away from the relational model to other, more flexible models.
- Learning Objectives
- How to generate a Data Model or Data Dictionary
- Customize the Open in Microsoft Office menu
Developers can use the App Engine Datastore to develop applications that access data from anywhere in the world.