0
votes
1answer
47 views

Table design for departments with duplicate department numbers

How should I define a database table for my company's departments where we have a set of duplicate department numbers used by an upper management tier. dept_id dept_name manager_id ...
1
vote
1answer
116 views

Concept, ER model, “employee tracking”

I have to achieve 3NF with following specifications: Database where I will determine priority skills for specific jobs and track the employees ability to complete them. Train employees. Example of ...
0
votes
0answers
59 views

Advice on DB Changes

Requirement: I'm required to prefix a couple of strings to the existing one. I.e. The current way, the Accounts been recorded in AccountID field looks as "621230093VN00" Under the new format ...
3
votes
1answer
98 views

Temporal Validity and Primary/Foreign Key Relationships

I've read through several of the oracle tutorials that show temporal validity and time features. However, in the examples I'v read, there is no primary key used in the demo tables. ...
4
votes
2answers
162 views

Is it bad idea to create foreign keys on the tables in a different schemas in the same database for big applications?

I am working on the transfer a big pl/sql web-based application to the dedicated server. This application is located in one schema with 70 packages of programm code. This application was made ...
2
votes
1answer
162 views

Relationships between subtypes of an entity

I have a very big (hundred of milions rows) table which represent an entity (event) having many subtypes (different kind of recorded events). Each row is identified by an id (obj_id) and has an ...
2
votes
2answers
206 views

When is it appropriate database design to use a separate schema?

I am developing a database which has data sourced from many different applications. In my first pass at design, I placed the staging tables each in a schema named for their source application. As ...
-3
votes
1answer
625 views

How to retrieve a query from 3000 tables in SQL? [closed]

I have plan to create 3000 tables for my database. But I am afraid how will I retrieve some data from those many tables. Eg: Select * from table1, table2, table3.... table3000 where ...
0
votes
1answer
222 views

Database Design for lookup tables

I have a transaction table called questionnaire and a question lookup table called question_codes. questionnaire qst_id - number (primary key) question_type - number (foreign key) ...
1
vote
0answers
75 views

How to store object with property of generic type in DB

In my business domain there exists an entity parameter. Beside other properties which are identical for each parameter, they have values. These values can be of different data types. There might be ...
0
votes
1answer
544 views

Oracle db_name, db_unique_name, and ORACLE_HOME

I'm studying Oracle and I would like to understand the uniqueness or constrains when assigning these parameters. Suppose a machine with a single OS (host) and 2 different databases (physical). Are ...
1
vote
1answer
1k views

Oracle database created whats next? Create Schema or Create Tablespace?

I'm a programmer that's being thrown to the DBA/Sysadmin island all by myself with a volleyball that I named Wilson. I'm trying to survive here. I'm supposed to create a database for an application. ...
0
votes
1answer
114 views

List partitioning or filtering a non-partitioned table, which incurs lower cost?

Consider a list partitioned table PARTITIONED__SOURCE_TBL on column period, and non-partitioned table SOURCE_TBL also having the same columns. Assume all tables are adequately indexed. Set 1: ...
2
votes
1answer
149 views

Expanding parent-child tree with additional tables

I have readonly access to a table (Oracle database) which contains a hierarchical structure, represented via parent-child. I need to add another table, which will expand the existing hierarchical ...
5
votes
1answer
294 views

Enforce data integrity with a recursive table

I have an existing oracle 11g database schema that works with a web application. I am planning an expansion to the application so a web service can do data operations on the database. As part of the ...
1
vote
1answer
317 views

Possible reasons for slow BLOB updates in Oracle?

We have an application that transfers .bmp images as BLOBs to an Oracle table. One of the users of the app has complained that it is taking 2-3 seconds to transfer each image. Each image is ~1.2Mb. ...
7
votes
1answer
205 views

Designing Simple Schema for Disaggregation of Demand Forecast

I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case: I have a parent-child hierarchy of products (example, Raw ...
5
votes
3answers
880 views

Partition pruning with multiple date columns

I have a large table in Oracle 11g database that holds historical data from several years, so I would like to partition it by year. The problem is that the table has multiple date columns and they are ...
0
votes
1answer
597 views

How shared nothing and shared everything architectures look like in Oracle?

Oracle has partitioning and clustering features. Partitioning enables to split table or indexes into multiple tablespaces and store on various servers. So it's done manually. Clustering enables ...
2
votes
2answers
160 views

Support ticket system - when/how to move old tickets?

I don't have access to the partitioning feature, but consider a support ticket system with tens of thousands of tickets being opened everyday and taking about a week to a couple of months in getting ...
0
votes
1answer
611 views

The four properties of a primary key

In our dbms class, these were the four properties of a primary key discussed for Oracle. Unique Not NULL Fully functional dependency Indexed I understand all the properties except the 3rd one ...
2
votes
2answers
278 views

Should I use SQL or NoSQL for this specific design (surveys)?

Building a small web application to create satisfaction surveys with a default list of questions and responses. The application is for a small audience of people (5,000 max) with surveys sent out when ...
1
vote
1answer
258 views

DB stored procedures: how do they help increase security in web systems?

The usage of Oracle PL/SQL procedures for controlling data access is often emphasized in PL/SQL books and other sources as being more secure approach. I'v seen several systems where all business logic ...
6
votes
1answer
7k views

What happens when you modify (reduce) a column's length?

Lets say I have two columns of type NUMBER (without precision, and scale) and VARCHAR(300). I saw that these columns are way too large for my data, so I want to modify them to NUMBER(11) and ...
-1
votes
0answers
304 views

Synchronising SQL tables in two locations [closed]

We have a bit of a conundrum at the moment. ANSI SQL solutions please. We have two databases located at two different geographic locations and will be using the web for all traffic between the two ...
1
vote
1answer
92 views

What is the best relationship model for high performance?

I want to model a relationship between Customer and Transaction. However, because Customer is only described by a unique string and no extra info, I have two possibilities to implement it in the ...
6
votes
4answers
428 views

Can this business logic be enforced by a conditional database constraint?

I am trying to duplicate the business logic embodied an intranet C# web application in the database so that other databases can access it and work under the same rules. This "rule" seems difficult to ...
11
votes
4answers
558 views

Reason to not use nullable number in Oracle?

Our company is interfacing with another software company for a joint project, and we were told that, if a particular value should not be displayed, we should pass in a -5000 (their arbitrary sentinel ...
1
vote
2answers
2k views

Does order matter for composite primary key in Oracle or any database?

I have a Composite Primary key which is combination of Column1, and Column2. What would be the difference if I have Composite key with Column2, and Column1. Does it even really matter which order ...
3
votes
2answers
382 views

What exactly is “pinning” in relation to indexes?

When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions. It is part of a test question, where ...
5
votes
3answers
495 views

How to store many smallish time-series in a relational DB?

Input Data Multiple Tests Beds generate measurement data of various complexity. In it's most basic form, not considering any meta-data, one measurement on a Test Bed will be a small (1 - a few ...
2
votes
1answer
212 views

Oracle ash report

Recently, i have been handed over with an ash report from DBA. To me this report is like french. I have no idea what it is about and what all is written in this report. Can someone please guide me in ...
3
votes
6answers
15k views

How to export an ER diagram from an Oracle DB?

I am developing this application with an Oracle database. I haven't been documenting the data model, but now an auditor wants to have an entity relationship diagram of my database. There are many ...
6
votes
2answers
890 views

Database for building a realtime analytics system

I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on Real time counting of unique URI visit/PV by different ...
2
votes
2answers
599 views

Is to possible to forbid access to tables in own schema - Oracle?

Lets assume that we have the following situation: We have an Oracle schema/user: USER_TEST (with the "create session" privilege). SYS as SYSDBA creates couple Tables on schema USER_TEST. Is it ...
1
vote
2answers
478 views

database normalization 2nf, 3nf

I am doing homework about database normalization. I have to decompose the table into 3nf form showing the steps here is the initial table: ...
2
votes
2answers
548 views

Logical schema development and migration tool for different physical schema available including triggers and stored procedures?

We are planning to support for our application another rdbms (oracle). At the moment we are design our database schema with the MySQL-Workbench. For every new database version we have schema ...
0
votes
1answer
294 views

Oracle: Extract Graphical Database Representation [duplicate]

Possible Duplicate: Oracle Creating ER Diagrams and Data Dictionaries How can I extract a graphical (schema) representation of an Oracle database (tables and their relationships; with ...
0
votes
1answer
420 views

What are the other types of database architectures? [closed]

Oracle, for example has a typical client server architecture, and a memory cache (SGA) with some background processes. This is one way of designing a database. Are there databases out there which ...
2
votes
1answer
96 views

How to make a duplicate field cheap?

There are two tables associated with each one to many in a field. In Table 2, there are fields that duplicate the value in Table 1. Is there a cheaper way to ensure (guarantee) the correspondence ...
13
votes
5answers
11k views

Oracle Creating ER Diagrams and Data Dictionaries

I am new to Oracle Databases and would like to develop a data dictionary and ER Diagrams for our existing databases. Do you have any tips, scripts, tools for doing so?
10
votes
2answers
2k views

Security for Application Developers doing PL/SQL work in Oracle

How do you handle the lack of Schema level privileges in Oracle? Oracle’s security architecture works well for applications that only need object level privileges and it works well for DBAs that need ...