FAQ Database Discussion Community


Script to concatenate columns and remove leading/trailing delimiter

sql,tsql,sql-server-2005,relational-database
I have a table like this and I want to return concatenated strings where the column values are in ('01', '02', '03', '04', '99'). Plus the values will be delimited by a ';'. So row 1 will be 01;04, row 3 will be 01;02;03;04 and row 5 will simply be...

Database structure design for different categories

mysql,database,database-design,relational-database,directory-structure
I have two tables in my database...1st table named as categories contains all the parent categories and sub categories...as shown in the figure below... Categories_id name Parent_Categories 1 art 0 2 entertainment 0 3 music 2 4 dance 3 5 songs 3 now i have to create a 2nd table...

How to add a N:M relationship to my MySQL symptoms and diseases database?

mysql,database,database-design,relational-database,relationship
I am working on a health app and I've created a database (MySQL), which stores symptoms and diseases. There should be a N:M relationship between the symptoms and diseases, so each symptom could have multiple diseases and vice versa. Symptoms should also have common and less common diseases and because...

MySQL Modeling: Relationship normalization & ring issue

mysql,relational-database,primary-key,relationship
I'm building this MySQL model to organize a movie collection. It will manage both movies and series/episodes. Each movie/episode may have one or more audio stream and codecs for audio/video may vary. (don't mind subtitles for now) I'm not an expert, so I'm seeking advice: Ring relationships. Someone told me...

MySQL Auto Insert Record Into TableB when TableA record is created

mysql,sql,database,tsql,relational-database
I have two tables. msUser and msUserChar. I have created a relationship between msUser and msUserChar where the PK in msUser is a FK in msUserChar What I need, is when a record is inserted into msUser, a record is also created in msUserChar where it's FK is the PK...

Two ruby models. has_one, and belongs_to. Why have two models at all?

ruby-on-rails,ruby,relational-database
I'm building an app that currently has two models: User and Income. Every User has one Income, and one Income only. Each Income must belong to a User. When a User is created, so is their Income. There should be no Income creation without a User assigned; and as above...

postgresql foreign key syntax

sql,postgresql,foreign-keys,relational-database,foreign-key-relationship
I have 2 tables as you will see in my posgresql code below. The first table students has 2 columns, one for student_name and the other student_id which is the primary key. In my second table called tests, this has 4 columns, one for subject_id, one for the subject_name, then...

Listing specific requirements on Access Database form

database,ms-access,access-vba,relational-database,entity-relationship
I am trying to create a database using Microsoft Access Database, but I have a problem. I want to create a database that holds information for computer components and a form to list certain components to build a computer. For example, I want to build computer A, and I have...

Can a Field act as both Foreign and Primary Keys in the same table?

database,foreign-keys,relational-database,primary-key,flask-sqlalchemy
I am working with Flask-SQLAlchemy, but I guess this question cuts across different database implementation platforms. Suppose I have two table models structured as: User (with fields: user_id as PRIMARY KEY, username, email, password) and UserDetails (with fields: f_name, l_name, dob, gender, address, contact, bio and user_id as FOREIGN KEY)....

How to have multiple rows reference the same field of a single row?

relational-database,relational
I'm new to PostgreSQL and forming relationships between tables. I have a jobs table and an items table. The jobs table has a shipping_date. I would like the items table to have a column that reflects the shipping_date of the jobs table: jobs table: job_id | shipping_date 10001 | 29-06-2014...

Rails HABTM or has_many?

ruby-on-rails,many-to-many,relational-database
I have two models: Person, and Property. In the Person model I have a field which stores the role of the person(tenant, owner, admin, contractor, etc). Since each property will belong to an owner and potentially have one or more tenants, I thought this would be a good opportunity to...

Description of PostgreSQL database tables installed on a particular computer

postgresql,relational-database
A bit over a year ago I installed PostgreSQL on six computers. On one of those computers, I imported 2 gig of census data from CSV files. Historically I have just worked with flat files, but in this case the files are so big they choke my analysis software. I...

How to link two relational tables in mySQL

mysql,sql,relational-database,database-schema
I have two tables Video (v_id, v_duration, v_status, upload_date) in the video table the primary key is v_id. video_tags(tag_name, video_id) The video can have many tags, that's why I separated them into two tables. The question is when I create these two tables using mySQL database, I managed to create...

Unique usernames in 2 tables at different levels. Suitable database design?

database-design,relational-database,database-schema
I have a table with suppliers. This table contains, among other things, name, username and password hash. Sometimes a supplier is just one person, in which case this works fine. But sometimes a supplier is a company and in that case, multiple employees may need to log in. Until now,...

Database Join Logic for finding exception member

sql,relational-database,inner-join
I have 3 table User (UserID) ROOM (RoomID, UserID) ---the userID is the host RoomMember (RMid, RoomID, UserID) ---the UserID is the people inside the room This is an example of 1 user host a room . Then the Room member is those who inside the room. now, i want...

SQL Query to display unique records based on certain conditions

mysql,sql-server,relational-database
[I have gone through a large number of questions before posting this question.] I have a table which contains 4 fields. It is ClientId, ClientName,ClientAddress, ClientCity. Now, I have an autocomplete textbox control where I need to fetch & display client name. The problem is that in our database we...

How to implement a one-to-many relationship with an “Is Current” requirement

sql,database,entity-framework,database-design,relational-database
Designing a database, there's a relationship between two tables, Job and Document. One Job can have multiple Documents, but one (and only one) of these Documents needs to be flagged as IsCurrent. This is not always the most recent Document associated with that Job. Structurally, I can see two ways...

Database relation

relational-database,database-schema
I'm newbie in databases and I'm designing database and struggling with one problem. So in my application there are trainings which could have many students and also those students can take a exams in those training and gets grades for it. So I come to conclusion that I got many-to-many...

Identifying non-trivial functional dependencies in database tables

database,relational-database,database-normalization,functional-dependencies,bcnf
Which non-trivial functional dependencies can be discovered in this table? Are there any systematic approaches that can be used to do so? | first_name | surname | student_id | program_code | name_of_study | ...

Oracle: Best practice to handle Flag

database,oracle,database-design,relational-database
I have designed an ER diagram for a DVD shop which allows rentals only to registered customers and purchases can be from anyone. A DVD can obviously cannot be rented after it is purchased. Every DVD can be purchased or bought. Question: What is the best way of enforcing in...

Relational data modeling for sub types

database-design,relational-database,data-modeling,subtype,relational-model
I am learning the Relational Model and data modeling. And I have some confusion in my mind regarding sub types. I know that data modeling is an iterative process and there are many different ways to model things. But I don't know how to choose between different options. Example Suppose...

Using AWK to perform math on columns of data

bash,awk,relational-database,symbolic-math
Using AWK, I'm trying to take a column of information as input, perform math on the input, and then return the results back into the column. My input file is in the format as below: 1, 1, 6, 3, 2, 4, 1.5, 1, 0.5, 1, 2.75 2, 3, 7, 6,...

How to create default relationship for 2 MySQL tables with many-to-many relationships?

mysql,sql,database,many-to-many,relational-database
In MySQL, I have 2 tables, A and B, with many-to-many relationships. And I have a pivot table, A_B, for the relationships between table A and B. In table A, there are some rows that I want to link to *ALL* (including current and future) rows in table B. For...

Model relationship many to many

sql,sql-server,relational-database
I have an Excel file which contains ProcessSheet,InputsSheet ,the process sheet and the input sheet are defined in columns like this idProcess | Name | InputsIds | InputsNames idInput | InputName | Comments how can i model this schema in Sql Server...

How to implement certain relation between two entities? [closed]

c#,asp.net,asp.net-mvc,entity-framework,relational-database
I'm writing an app in asp.net MVC, and want to make models for my database using entity framework. I want to make two entity models, song and playlist. One playlist can, naturally, contain many songs, I suppose in that case I want to include public List<Song> Songs { get; set;...

Should I give all my relations associations through?

ruby-on-rails,relational-database
Is it considered bad form to give the relations between my tables a :through association? e.g. if I had multiple shows each of which has only one performer, and I want to give be able to say something like aShow.performer, is that sufficient reason to set up a :through association?...

MySQL: Alter table to set Foreign key

mysql,foreign-keys,relational-database
Although there are some similar questions about the subject, I can't find the right answer for my problem. I have 2 tables called customer and car. What I want to do is this: When I delete a customer from database, I want the car that belongs to that customer will...

Database design and query for a aviation training center

mysql,sql,database-design,doctrine2,relational-database
I work for a company that rents sessions in aircraft simulators for clients. Before getting in the simulator, pilots (the clients) must have a valid formation for the simulator he will pilot. A valid formation is a formation sucessfully passed by the pilot and not expired (so we have to...

Saving to relational table Laravel 4

php,mysql,laravel,relational-database
Basically I have a groups table with id and name, I have another table with users_groups with id, group_id and user_id I'm try to save when a user is created to the users_groups table, but when I use this code it saves what it can in the groups table. Is...

Confused on SQL Relationships

sql,relational-database
I am confused in SQL Relationships, specially with One to One and One to Many. I have read many articles like this and this For an example, below are some statements from those articles and some other articles The relationship between customer and address tables are one to one, that...

Database Design: How should I store user's news preferences in MySQL database?

mysql,database,database-design,relational-database
I'm trying to implement a personalized news aggregator. I have to save user's preferred news sources in db. Should I store all the news sources liked by a user as JSON string and then decode it after retrieving? feeds | user or have individual column for each news source(no of...

How to store custom entity properties in a relational database

sql,database,database-design,relational-database
I'm designing a database for a application I'm developing. I have two tables where the entities can have custom application properties that will define how the data from each entity is used. Each entity can have zero to many properties and i want to know what the best way is...

How to choose between relational database and non relational database?

database,relational-database,non-relational-database
I have to build a website that store a lot of data. I search in the Internet to decide whether to use relational or non relational database. I can't find a good answer. Some website say that if you have a lot of data you can choose non relational database,...

SQL Server : Insert into Relation Table

c#,sql,sql-server,insert,relational-database
I want to insert a tuple in my relation table named R_Kitap_Al. But I get this error: System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'Kitap_Al'. But I don't have Kitap_Al table. I suppose this is my old name the table. because of that I get this error, but how can I fix...

Best way to create nested array from tables: multiple queries/loops VS single query/loop style

php,mysql,sql,relational-database,server-side
Say I have 2 tables, which I can "merge" and represent in a single nested array. I'm wandering what would be the best way to do that, considering: efficiency best-practice DB/server-side usage trade-off what you should do in real life same case for 3, 4 or more tables that can...

Add tags to photos mysql

php,mysql,foreign-keys,relational-database
I am implementing tag based search using PHP and MySQL where each photo has multiple tags. I will use this tags to search photos. For example, when I search for a query "building", I want all the photos that have the "building" tag. Here are my tables: photos(photo_id, name, url)...

Messaging table performance - Merge from and to id`s into one single field vs separate 2 fields

mysql,performance,database-design,relational-database,database-schema
What would be the implications on performance on merging the users id from the "messages" table in form of "fromto" - ex userID - 5, userID 9 => "59" versus having two separate columns "from" and "to"? Flow - list, read, delete messages when both id`s will be available in...

relational algebra

relational-database,relational-algebra
here is a example relation table I constructed from my relations. Assignment aID description 1 A1 2 A2 3 A3 #where aID is the unique key Group gID aID 1 1 2 1 3 2 4 2 5 3 #where gID is the unique key , represent groups works on...

SQLite how to refer to column name by a query

sqlite,sqlite3,relational-database
What I want to do basically is SELECT (SELECT fields FROM tableB WHERE interest=yes) FROM tableA Why I can not do this? What could be the workaround? Example: I got tablea like this time|c1|c2|c3 --------------- 0.0 |1 |11|111 1.0 |2 |22|222 2.0 |3 |33|333 tableb like this field| interes --------------...

Payment tables design

database,database-design,relational-database
I have 2 tables Payments and PaymentMethods. However depending on the payment method, some card details won't be stored and some extra details may. So the card details stored depend on the payment method - for example with wire transfer. What is the best way to design this? +--------------+ |...

Foreign key constraint on SailsJS

database,node.js,relational-database,sails.js,waterline
This is my College model module.exports = { attributes: { name:{ type:'string', required:true }, location:{ type:'string', required:true }, faculties:{ collection:'faculty', via:'college' } } }; This is my Faculty model module.exports = { attributes: { name:{ type:'string', required:true }, description:{ type:'string' }, college:{ model:'college', required:true } , years:{ collection:'year', via:'faculty' }...

Unable to get correct data over several joins

mysql,database,join,relational-database,jointable
I have the following tables and sample data: Table: activities +------------------+---------------+ | activity_id (PK) | activity_name | +------------------+---------------+ | 18 | Bicycling | | 19 | Running | +------------------+---------------+ Table: activity_attributes +------------------+-------------------+ | activity_id (FK) | attribute_id (FK) | +------------------+-------------------+ | 18 | 55 | | 18 | 56 |...

Salesforce: SOQL to query Master that has at least X amount of details associated

salesforce,relational-database,soql
I'm trying to set up a query that will return which contacts have over 5 deals with us. We have a custom sObject which is (terribly) named, contacts_deals__c which serves as an intermediary table between contact and deals__c. Contact is the master sObject, Contacts_Deals__c is the detail. Return all Contacts...

Translating ER diagram to Relational model

database,relational-database,entity-relationship,erd
I have a question about the ER diagram below. Question: If there are 2 relationships born and live from actor to place entity. Do they both have the same foreign key which is place_no? So does that mean I just put the foreign key in Actors relational model and that's...

Handling duplicates while updating records in SQL using where clause

sql,database,oracle,relational-database,sqlplus
I have a situation where i need to update a row in table and when faced with a duplicate entry then take a decision based on another column value. For example let's say my table is like this : salary_table Salary Username Usersurname Last_entry_date 3000 abc bak 20-feb-13 4000 sdf...

Does my table need more normalization?

mysql,database,relational-database,normalization
I am making a simple public cashbook DB table to calculate how much my friends and I paid for having meal together. My first table was only a single table and it had a column "person" with non-atomic values, so I divided my table into two tables like above....

Which one should be preferred 1:1,0 or nullable fields on one table on relational database?

entity-framework,database-design,orm,ef-code-first,relational-database
I use Azure Sql and Entity-Framework Code First. I have Transaction Table. There are 4 types which are buy/sell, withdrawal, deposit and fee. For example, I need DepositCode for deposit transactions but this column will be null for others. Also I need ItemPrice and ItemAmount for buy/sell but it will...

ER Diagram based on multiple databases & tables which derive names from column values from other table

mysql,relational-database,erd
So I have a Learning Management System. There are total 5 databases. lms_data_db lms_course_db lms_test_db ..n two more now whenever a row is added under lms_data_db.course_data say with course_id=200, a table named course_200 is created under lms_course_db. likewise when a row is added under lms_course_db.course_100 with chapter_id=100, a table named...

Database for 'who viewed this item also viewed..'

mysql,database,mongodb,database-design,relational-database
I want to create feature 'who viewed this item also viewed' like Amazon or Ebay. I'm deciding between MySql and non-relational database like MongoDB. Edit: It seems to be straightforward to implement this feature in MySql. My guess is creating 'viewed' table in which userId, itemId, and time of viewing...

SQL : Return column value based on previous few rows values

sql,sql-server,relational-database
I have a table something like this NAME TIME IsTrue A 15-05-2015 02:00 0 B 15-05-2015 03:00 1 C 15-05-2015 06:00 0 D 15-05-2015 13:00 0 E 15-05-2015 23:00 0 F 16-05-2015 04:00 0 E 16-05-2015 07:00 1 G 16-05-2015 23:00 0 I am trying to write a query whose...

mySQL structure/relation ideas

mysql,database,relational-database
I have two tables: 1. Person 2. Record Multiple rows from table person can be connected to one row from record. But if i have one person that is connected to multiple records, what is the best way to connect this? Record no.1 = Person no.1 , Person no.2, Person...

SQL Server : UPDATE Relation Table

c#,sql,sql-server,relational-database
I want to update my relation table between book and user named R_Kital_Al But I get this error ; System.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.R_Kitap_Al' with unique index 'IX_Kitap_Al'. The duplicate key value is (15). How can I fix this error? private void Update_Click(object sender, EventArgs...

Mongodb how to store ratings that users have given to database objects

mongodb,relational-database,meanjs
I am trying to lay out the architecture for a MEAN.js application that I'm working on and I'm having some trouble deciding how to store some data. The system will allow users can log in and rate weekly cleaning chores on how happy they would be to have a specific...

Java XML format to save directly to relational or non-relational DB

java,xml,relational-database,non-relational-database
Is there a standard XML format to save Java objects into relational or non-relational databases? I am looking for something in Java akin to how you can save JSON directly into MongoDB (as BSON). It doesn't have to be a non-relational database, something with an ORM process would do. So...

Ensuring a webapp user can only add records to a database that they own

mysql,database,security,web-applications,relational-database
I am working on a very simple web application to log teachers, students, and homework each in their own table in a MySQL database. A student is assigned to a teacher and a table logs which homework tasks have been completed by which students. A teacher can select a certain...

Algebra Relational sql GROUP BY SORT BY ORDER BY

sql,group-by,sql-order-by,relational-database,algebra
I wanted to know what is the equivalent in GROUP BY, SORT BY and ORDER BY in algebra relational ?

Django queryset on related field

django,postgresql,relational-database,django-related-manager
Django is to making a query much more complicated than it needs to be. A Sentiment may have a User and a Card, and I am getting the Cards which are not in the passed User's Sentiments This is the query: Card.objects.all().exclude(sentiments__in=user.sentiments.all()) this is what Django runs: SELECT * FROM...

Managing SQL schema in F#

sqlite,orm,f#,relational-database,type-providers
Because of the presence of type providers for accessing SQL data in F#, there's not much focus on the use of ORMs, micro or otherwise. I can definitely see the logic behind this. It also seems that many examples of using F# to play with relational data is to plug...

Visual Studio 2013 Report RDLC with related datasets

visual-studio-2013,report,relational-database,rdlc
I currently have two tables in a database "customer" and "pricelist" where a customer can have multiple pricelist items linked to their ID. I'm trying to build a report in VS2013 where it lists all customers [alphabetically], then all of their pricelist items [alphabetically], then page break between each customer....

Multiple Ruby on Rails Model Relationship

ruby-on-rails,ruby,relational-database
I am trying to access location details thru a shipment model that has an origin and a destination. I would like to access the location information thru a call like this: Shipment.find(###).origin -> :id => "###", :cs => "Springfield, Mo", :latitude => "#####", :longitude => "####" Shipment.find(###).dest -> :id =>...

MySQL Multiple tables or multiple values for single field?

php,mysql,database,database-design,relational-database
This is a problem I've had for a while and not sure what is the best way to solve it. Its probably easier for me to explain using an example. So I have many collections and many items. Some items can belong to one or more collections or none at...

laravel two identical belongsTo() relations, one returns an array another string

mysql,database,laravel,relational-database,belongs-to
I don't get it I have two foreign keys identically set and two belongsTo() functions but one of them doesn't work right, instead of returning an array it just returns a string value from database. Here are the tables that I have in database. Table pages ----------------------------------------------------- | id |...

One to One ER with a Type or Status table instead of a Type or Status column

database,database-design,relational-database,entity-relationship
I have a long way to go with ER modelling, as you'll soon see, but I have a project I'm doing for a construction firm and I'm getting my hands wet with the database model. The company is a roof inspections company and they have projects on sites and proposals...

Best storage option for temporary data

nosql,relational-database,storage
In the context of an application I am developing I need to store quite a good amount of data for limited amount of time - think of the scenario in terms of object buckets - for a certain limited amount of time objects are in some buckets then they move...

Using relational division in mysql on a simple associative table

mysql,sql,relational-database
We have an associative table that connects threads with tags. We are trying to figure out a query that would give us any thread ID's that are tied to a combination of tag ID's. I created an sqlfiddle at: http://sqlfiddle.com/#!9/c8ebe/3 So the table is structured like this: CREATE TABLE `threads_tags`...

Proper database model for a user feedback system (an interesting case)

mysql,database,database-design,relational-database,foreign-key-relationship
I am developing an application using PHP and Yii Framework. I've been thinking about the most suitable database structure for the given functionality and here's what I've come up with. Yet I'm not 100% positive that's how it should be done so I've decided to ask the community. App...

SQL query that produces the following output?

mysql,sql,relational-database
I am trying to write an SQL statement producing the below output. I have the two following tables: UserMovie userID | movieID ----------------- 135 | k0jps 135 | p1zka 125 | v0t67 115 | opp2s 111 | xnwri 115 | kspdl Follows followerid | followingid ------------------------ 122 | 135 192...

The three schema of the database

database,database-design,relational-database,database-schema,entity-relationship
I have created a database in Access and right know i have to write a report. I know that the databasesystem has three forms of schemas: physical, conceptual and external. Does the following ER diagram (by using the method normalization) belongs to the conceptual level?: Or does this belong to...

BEST RELATIONAL DATABASES APPROACH FOR DELETE: use DELETE or use a valid/non-valid COLUMN?

mysql,database-design,relational-database
When it comes to databases, which is the best technical approach to use when a user want's to delete his profile/delete some of his products from a shopping card: Actually delete the rows from the relational tables that have his id, using DELETE. Don't DELETE any row, but change the...

MySQL Exclusive JOIN with multiple values for one column

mysql,database,relational-database
I have the following structure in MySQL: business ------------- id name service ------------- id name business_service ----------------- business_id service_id I want to select the business that have not one but all the specified services. If I do something like this: SELECT * FROM business LEFT JOIN business_service ON business_service.business_id =...

Hibernate Storing Fields inside Classes

java,hibernate,orm,relational-database
I have two classes with two individual tables, "Employee" and "Company". I would like to keep a list of employee inside Company class. It is easy but I do not know how to represent this list in the database side. Class 'Company': @Entity @Table(name = "company") public class Company {...

django party rental company inventory model

django,model,relational-database,inventory
A party company rents out chairs, table and tents. These all have a set price, but there are savings if the items are bungled in a package(e.g. Package A includes: one 18x30 tent, 72 chairs and 12 tables). I'm dealing with three different packages: a, b and c. My model...

How to create database tables for a form-generator and its record?

mysql,database,database-design,relational-database
I am working on a form-generator, however , I was confused when attempting to create the database for it. The requirement is divided into backend / frontend, For backend, the admin can manage the form specific form name manage the form input (only input box , select box , check...

Surrogate Keys complicate insertion?

database-design,foreign-keys,key,relational-database,surrogate-key
I often see people using artifical / surrogate keys in relational databases. Thinking about it, it seems to me that while this simplifies join-Queries, it complicates the insertion of new tuples. Take the following example: R1(a, b, c) R2(c, d, e) c is a surrogate primary key of R2, referenced...

laravel 4 : insert new recode into a one to one relational tables using eloquent

laravel,laravel-4,model,relational-database,eloquent
ex: there are two tables which are related each other one to one one table is users other one is students users- primaryKey-'uid',name,age students- primaryKey-'std_id' , faculty , gpa and this is the model for students table class Student extends Eloquent { protected $table = 'students'; protected $primaryKey = 'std_id';...

Join 2 SQL tables and display in 1 PHP Table

php,html,mysql,database,relational-database
I want to join 2 SQL tables with same attribute but different data and display in 1 PHP Table. Table w2 and w3 and display in 1 PHP table. I am new to PHP so the Code might be very wrong. <?php //create database connection require_once('C:\Users\TO115956\Documents\EasyPHP-DevServer-14.1VC11\data\localweb\projects\...\db_w2.php'); require_once('C:\Users\TO115956\Documents\EasyPHP-DevServer-14.1VC11\data\localweb\projects\...\db_w3.php'); //query the db...

Duplication of data in explicit authorization

permissions,authorization,relational-database,rdbms
Our current authorization strategy on our site is very tightly coupled to our RDB's schema - which in some ways is a good thing, since it means the permissions available to a user exactly match what he should have, assuming a correct interpretation of the data. So when we query...

How to reference groups of records in relational databases

relational-database,normalization,referential-integrity
Suppose we have the following table structures: Humans | HumanID | FirstName | LastName | Gender | |---------+-----------+----------+--------| | 1 | Issac | Newton | M | | 2 | Marie | Curie | F | | 3 | Tim | Duncan | M | Animals | AmimalID | Species...

Trouble filtering information with relational databases in MySQL/PHP

php,mysql,database,relational-database,foreign-key-relationship
I'm working on my first project (a project management system) to understand PHP and MySQL better. I currently have 3 tables in my database, one that lists all the projects ('projects' table), one that stores the users username and password ('users' table) and then a third, small 'permissions' table that...

Finding the Candidate Keys of a Relation Using the FD's

relational-database,relation,relational-algebra,functional-dependencies,candidate-key
I have definitely checked out many different related posts, as suggested when creating this question. I have also done different sample problems from online sources as well from a similar problem. However, I am stuck on the problem below specifically. Given the following relation R and the set of functional...

SQLite database design for music chart tracker

bash,database-design,sqlite3,relational-database
I've been putting together a little SQLite database to track the top 100 songs from the iTunes RSS feed. I've built the script in Bash to do all the hard work and it's working finally, but I'm not sure if my database structure is correct, so I'm looking for some...

How do I create a 4VL “boolean” type in PosgreSQL?

postgresql,logic,relational-database,relational-algebra
I am looking for a type that would have both TRUE and FALSE as per the normal boolean type, NULL (also as per the existing implementation, Codd's "A-Value" or Applicable unknown) and NA (Codd's "I-Value" or Inapplicable). Example: imagine you have a set of medical tests for patients as attributes...

Database Design Advice for a Social Network App Needed

database,database-design,database-connection,relational-database,database-schema
I am very new into backend stuffs like databases. That being said, I lack the database design knowledge. I am wondering how and what is the best design for my scenario. I am creating a social network app where the users can create groups and join other groups. Those groups...

Does this look like a proper database design?

database-design,relational-database,database-schema
I've been working for a few weeks trying to read about all the in's and out's of designing a proper database structure. After many attempts, I think I may have what is considered correct. But i'm skeptical on whether or not I have joining tables and foreign keys setup correctly....

Is it possible to tell if a database is normalised just by looking at the ERM

relational-database,data-modeling,database-normalization
Is it possible to tell if a database is normalised just by looking at the ERM? What assumptions do you have just by looking at the ERM

One-to-many relation based on multiple key (conditional) in Entity Framework

.net,sql-server,entity-framework,relational-database,one-to-many
So I have class User { ... public virtual ICollection<Book> AlreadyRead {get; set;} } class Book { ... public virtual User Owner {get; set;} public bool AlreadyRead {get; set;} } So what I need is that User.AlreadyRead return me Books by this User, where AlreadyRead == true I tryed this...

How do I group entry timestamps into visitor-specific “sessions” in PostgreSQL?

sql,postgresql,relational-database,web-analytics
Here's some mock data: visitor_id,channel,timestamp,order_id,session 100,A,1,,1 100,B,2,,1 100,A,3,,1 100,B,4,1,1 100,B,5,,2 100,B,6,,2 100,B,7,2,2 100,A,8,,3 100,A,9,,3 A visitor will come into the site via channels, and eventually order (creating an order_id). Many visitors never order, but I still want to group their session together (to determine what was their first channel, for...

Ways to represent multivalued elements in MySQL table

mysql,database,relational-database,database-normalization
I'm trying to find the best way to make a table, eliminating the problems of redundancy and make it modular. For example, suppose I have the following tables: Company(id, name) { 1; IBM 2; Google 3; Amazon } Services(id, name) { 1; Phone 2; Gas 3; Electricity 4; Water 5;...

How to add new relations and making weight dependent in MySQL database?

mysql,database,database-design,relational-database,database-schema
I am working on a health app and I'm designing a database (MySQL), which should store symptoms and diseases and bodyparts. The app will work as follows: User chooses gender, age and bodypart (front/back) App shows all (common/less common) symptoms of chosen bodypart User chooses symptom App shows all (common/less...

how to convert if-statement in to switch statement. (textbox properties)

c#,relational-database
I have a question regarding with C# - programming. I'm working out on how to make my if statement into for loop or Switch. P.S. The reason I ask this because I don't know how to make a switch statement that conforms with this sample if Statement if (!string.IsNullOrEmpty(cmbDivision.Text) &...

How to set up Primary Keys in a Relation?

database,database-design,relational-database,database-schema,entity-relationship
I wish to know how to correctly set up Primary Keys in a Relation. E.g. we have ER-diagram which contain elements: Key attributes Weak key attributes Identifying relationships Associative entities In order to translate it into Relational Model we should do some tricks. All elements above deal with Primary Keys...

Relational Algebra and Confusing one Complex Examples

sql,database,database-design,relational-database,relational-algebra
We have 3-Relation: Students(sid, sname) Courses(cid, cname, dept) take(sid, cid, grade) We want to find student numbers of students whose these students take all courses that present in 'CS' department. why (line 4) is the answer of this query ? anyone could say differ from (line 1) to (line 3)....

Update a foreign key based it's value in it's primary table

sql,ms-access-2010,relational-database
Given the following two related tables how can an SQL query update a customer key of an order given the customer name. +----------+-------------+ +-------------+--------------+ | OrderKey | CustomerKey | | CustomerKey | CustomerName | +----------+-------------+ +-------------+--------------+ | 700 | 1 | | 1 | 'Idle' | | 701 | 2...

having one column related to 2 different fields in another table in access?

sql,database,ms-access,database-design,relational-database
I am building an access database for air freight for my company. I have a table with all the airports, and I have another table with air freight costs. The following are my fields for each table (a * means that it is part of a key) tblAirports AirportID*(Autonumber) AirportCode(Text...

Automaticlly attach to pivot table in Laravel 5

php,eloquent,pivot,relational-database,laravel-5
I currently have a Users to Groups Relationship (ManyToMany) with a pivot table group_user. I want the user to be able to create a group but once creating the group, how do I make it, that the creator becomes member of this group? Currently I have My Pivot Table (group_user):...

BCNF Decomposition?

database,database-design,relational-database,database-schema,bcnf
Given relation R(A,B,C,D,E) and FDs = {A->BC, CD->E, B->D, E->A} While converting R into BCNF: ABCDE -> BD & ABCE will be first decomposition BD is in BCNF Regarding ABCE: Opinion 1: ABCE has FDs: A->BCE, E->ABC, BC->AE Therefore its already in BCNF Opinion 2 ABCE has FDs: A->BC, E->ABC...

How to design an election poll database

database,relational-database
I want to design an election database for my country but I use USA as an example in this question, and assume that there are 30 political parties in USA. There are 30 parties. There are 50 states and 10 cities in each state in average(some states have 15 some...

Why does SQL standard allow duplicate rows?

sql,relational-database
One of the core rules for the relational model is the required uniqueness for tuples (rows): Every individual scalar value in the database must be logically addressable by specifying the name of the containing table, the name of the containing column and the primary key value of the containing row....

Laravel - Eloquent “Has”, “With”, “WhereHas” - What do they mean?

laravel,orm,eloquent,relational-database,relationship
I've found the concept and meaning behind these methods to be a little confusing, is it possible for somebody to explain to me what the difference between has and with is, in the context of an example (if possible)?

Functional dependencies for a relation with all prime attributes?

database,database-design,relational-database,database-normalization,functional-dependencies
I am doing a database theory course as part of my CS degree, and we have to confirm a database we designed is in BCNF normal form. I am currently trying to create the functional dependencies, but i am unsure of what to do when a relation is all prime...