Case Study: BCS, Part 2

The concept, piloted by the BSC PL/1 program, is to archive the data at the end of each enrollment period and clear the master tables (delete all records) in preparation for the next semester. This method eliminates the need to stamp each record with a year and semester. (Although this choice might seem nearsighted and not very extensible, it's used here to keep your case study from getting too complex.)

Each student is assigned a unique ID number. The student's name and major (again, keeping the case study simple) are also required for enrollment processing. Financial status is also required, but is held in a separate system. Obviously, each student is in one or more classes. Students who are not in any class are considered "dormant" and removed from the enrollment database for the semester being processed.

Each class is assigned a name and section (such as "CIS0100-10") that uniquely identifies it. Classes are filled with students up to the assigned class limit.

Professors, also assigned unique IDs, can be assigned to more than one class, but at BSC, no class has more than one professor.

BSC provides dorms for students who prefer to live on campus. Although each dorm can house upward of 500 students, students are allowed to live off campus. Therefore, some students do not have a dorm assignment.

What ORM-type statements could you pull from this information?

• Each student has a unique ID number.

• Students have a name, a major, and a financial status.

• Each class has a unique ID.

• Each professor has a unique ID.

• Students can be in zero or more classes.

• Professors can teach zero or more classes.

• Each class has only one professor.

• Each dorm has a unique ID.

• Each dorm can hold from zero to 500 students.

• Students may or may not live in a dorm.

Logical Design

Although you probably wouldn't sketch an ER diagram for each case study (see Chapter 8, "Creating the Logical Data Model"), if you start to see statements such as those in the BSC case study that get into detail about IDs, you might expect questions about the database schema.

As you work on the model, make sure you do the following:

• Draw the tables as you read the case study. Fill out attributes as they present themselves.
  
  
• Note primary and foreign keys on the diagram.
  
  
• Look for relationships between tables. Note the proper cardinality of each relationship as follows:
  
  

  • One (or zero) to many (1-*)

  • One to one (1-1)

  • Many to one (*-1) (reverse of first bullet)

  • Many to many (two one-to-many tables joined with a junction table in the middle)

You might not actually be asked to diagram a many-to-many relationship on the exam, but it's a skill you should know anyway.

If done properly, this drawing can serve to solidify your thoughts and make it easier to answer the exam questions without doing a lot of rereading. Figure 12.2 is a quick sketch of what the basic table structure might look like in your draft notes for BSC.

Figure 12.2. A logical database schema for the BSC enrollment system.

In the 70-100 exam, the questions about database schema often involved a "drag and drop" exercise in creating relationships between many tables. It appears that the 70-300 exam has pulled back on the complexity of these questions, creating fewer and simpler schemas. I also noticed a tendency to use "exhibits," in which the exam taker had to choose the correct schema rather than build it from scratch. Be grateful.

The other part of logical design is a system block diagram, possibly including interaction arrows between the boxes. For BSC, you might have drawn something like Figure 12.3.

Figure 12.3. A logical system design for BSC.

Physical Design

You are almost done. All that remains is bringing the theoretical design into the light of day to implement it in the physical domain. This process boils down to three areas: physical architecture, deployment, and maintenance.

In the exam, as in the real world, the physical design stage is usually an opportunity to draw another diagram, one that shows the connections of application components and the flow of data through the system. Additionally, you need to consider potential bottlenecks or latencies in your implementation. When reading the case study, keep an eye out for any of the following words or phrases: LAN, WAN, network, satellite, delay, bridge, phone line, or Internet. Also watch for statements that hint at the number of users (such as "from any of the 40 computer kiosks placed strategically throughout the campus" or "6,000 students from their own PCs").

Next, spend just a few more minutes with the client at Big State to see if you can gather the information needed to design the solution's physical layout.