Database Design

SQL Injection {Comments Off on SQL Injection}

By Winston L.

Database is a brilliant invention in this age of technology. From the Database page on Wikiperdia, database has existed for 45 years since when first proposed by Edgar Codd in 1970, however, not until the computer hardware’s capability became strong enough to handle large data processing were the database concept and database management systems (DBMS) widely implemented. After the birth of Internet, the demand for a decent application to manage large database increased even higher, and it is partly because people have found that database can be used to utilize everything, from business to scientific research. Nowadays, there are many major DBMS, such as IBM DB2, MS SQL, MySQL, and Oracle. All of them are based on the standardized SQL language, and that makes them vulnerable to one simple but very dangerous attack on the database, the SQL Injection. In this blog, SQL Injection attack technique, its impact, its victims, and prevention solutions will be discussed.

read more...

Logical vs. Physical Modeling {3}

The two authors of this article start off by declaring that data modeling serves as a link between business needs and system requirements. They stress that if we are going to work with databases, it is important to understand the difference between logical and physical modeling, and how they relate to each other. First, Logical Modeling deals with gathering business requirements and converting those requirements into a model. Also, it involves gathering information about business processes, business entities, and organizational units. After, diagrams and reports are produced (entity relationship , business process, and process flow diagrams). According to the authors, it’s important to note that logical modeling affects the direction of database design and indirectly affects the performance and administration of an implemented database. More options become available when time is invested performing logical modeling. Next, the authors go into Physical modeling. Physical modeling involves the actual design of the database according to the requirements that were established during logical modeling. Physical modeling deals with the conversion of the logical, or business model, into a relational database model. During physical modeling, objects are created based on everything that was defined during logical modeling. Other objects like indexes and snapshots can be defined during physical modeling. Physical modeling is when all the pieces come together to complete the process of defining a database for a business. The authors conclude that the importance of understanding the difference between logical and physical modeling helps us to build better organized and more effective database systems.

read more...

Databases Affect Many Aspects of our Lives {2}

The article I picked this week is titled “Ohio Audit Says Diebold Vote Database May Have Been Corrupted” by Kim Zetter. The article is talking about a company called Diebold who makes the electronic voting machines for the State of Ohio. It focuses on the November 2006 election in Ohio because there are questions to whether the database that these machines used was corrupted. The report cited by this article states that there were “Vote totals in two separate databases that should have been identical had different totals.  (Zetter, 2007)” The company essentially responded by saying that the system was working as intended. It was also found that “Tables in the database contained elements that were missing date and time stamps that would indicate when information was entered. Entries that did have date/time stamps showed a January 1, 1970 date.  (Zetter, 2007)” The article goes on to say that the database software that was being used was built by Microsoft’s Jet Database engine. It states that “The engine, according to Microsoft, is vulnerable to corruption when a lot of concurrent activity is happening with the database, such as what occurs on an election night. (Zetter, 2007)” The system was reported to be experiencing many problems during election night. It would reboot, crash, some ballots were not scanned, and some ballots were counted twice. Because of this several workers were convicted of tampering with election results.

read more...

Database Design And Recovery {1}

The article I read is called Database Recovery Options, by Lockwood Lyon. The backups of application data are very important and sufficient for any recovery needs. There should be a recovery strategy applied from the beginning, starting with database design. The author mentions that there are two things database designer must know; the recovery time objective (how long can the application data (or portions of the data) be unavailable?) and the recovery point objective (to what point must data be recovered? To a specific date/time? To the end of the most recently completed transaction?). It’s not only the database design that needs to consider recovery needs; applications that access the data must also be designed with recovery in mind. And at last, there should be a the infrastructure team supporting them. The author also mentions several database backup options for recovery; including database unloads, database full image copies, hot standby and Disk mirroring.

read more...

A Service Cloud Database {1}

In the article, the author talks about a  cloud database-as-a-service technology developed by Xeround that leverages the open source MySQL database. Xeround’s service can be described as a scale-out MySQL platform which enables organizations to cost-effectively solve database capacity issues that result from increased traffic and transaction volumes. Xeround CEO Razi Sharir said “Within the next few quarters you will see more and more Platform-as-a-Service (PaaS) providers delivering our database as the database of choice,” And Xeround will be the core back-end database for the majority of PaaS offerings. Xeround’s service is based on a pay-per-use model, but lacks support with data warehousing and business intelligence. Xeround database is a distributed structure, which means if a user wants to do a full table scan from multiple sources it will take a long time. The Oracle and VMware both enter the market, which expanded the database-as-a-service and increase the competition.

read more...

Importance Data Modeling {2}

The article that I read about talked about that in order for us to be good in working with databases it is important to acquire knowledge in security management, data integration and data recovery in the case of a disaster. Designing a good database doesn’t consist only in linking tables together; it requires lots of skills combined to be able to pull off a complete database structure that will run optimally. Many companies tend to fail when trying to design a good database because developing a good database model requires lots of time to create a well-model design and most of the companies don’t count with the necessary time to allocate to the data modeling phase.

read more...

Steps to Create a Good Self-tuning Logical Database Design {3}

The journal talks about logical database design and how database administrators have to maintain efficient databases to keep up with the current trends.  There is a lot of talk about self-tuning physical database design, but the self-tuning logical database design aspect is understudied.  The author of the article explores a framework in which a database should be able to self-tune its logical database schema that have SQL workloads.  Self-tuning of a logical database is necessary if it has to adapt and evolve to better match its user’s requirements.

read more...

Improper planned updates will hurt entire organization {3}

The article I read this week is “GitHub Says Database Issues Caused This Week’s Outage and Performance Problems” by Alex Williams.The article is saying that Github’s website had experienced outage and poor availability about a month ago. Company Github is a Social network for many programmers, it offers both private and public repository for many users. Team Github replaced old server to 3-node cluster in August, and new infrastructure is also designed based on new system. But the problem caused when high loads that Github never get. In result, some of private repositories were able to access to people who are not in the repository’s list for 7 minutes.

read more...

The New Cloud Computing By Oracle {5}

The article I read is about the new cloud computing provided by Oracle. Oracle introduced its cloud computing service last year. It can be leased by business and kept behind their own security firewalls. Last month, Mr. Ellison, the founder and chief executive of Oracle, kicked off his Oracle Open World gathering and announced Oracle has a new cloud database, called 12c, which is “a sharply different way of accessing a database in cloud computing.” Mr. Ellison’s claims that may indeed be “the first multi-tenant database in the world.” The new hardware from Oracle could double the speed of shifting data  from EMC, but costs only one-eighth as  machines from I.B.M. Mr. Ellison also delivered three important truths about cloud computing; all these big companies are taking cloud computing for business very seriously, they know how much big business values; Companies are going further down the food chain, Oracle is looking into offerings as good products for small and medium-sized businesses. Lastly, companies are focusing on improving the speed of data processing.

read more...

Database Design Mistakes {3}

A poorly designed database can lead to many problems down the road. It may not be apparent at first, since there is very little data, but as the database grows, you may start to experience inefficiencies and poor performance. Some of the seven most common mistakes that database professionals make include not spending enough (or any) time on documentation, little or no normalization, building before designing, improper storage of reference data, not using foreign keys or constraints, not using naming standards, and improperly choosing primary keys.

read more...